Using VB variables with Excel cell formulas. 2

[chrishooper]

Hi there,

I am trying to create Excel cell formulas that can use variables created during the execution of code in VB. In particular I would like to know if it would be possible to specify the sheet selection portion of the INDEX function based on a VB variable. For example,

If my basic INDEX function looked like this:

INDEX('Sheet Name'!D3:K6,1,1)

Could I replace the sheet name with a string variable defined in a VB subroutine like this

VB CODE:::
dim sheetname as string
sheetname = "Sheet no. 1"

EXCEL FORMULA:::
INDEX(sheetname!D3:K6,1,1) **this doesn't work yet**

I have thought about writing the string to an excel cell first in VB then picking it up with the excel formula but still without any joy. Can anyone tell me if such a procedure is possible? What syntax must i use to make it work?

Thanks in advance,

Chris.

 

[chrishooper]

a2 has my sheet name in it

 

[chrishooper]

hold up. INDIRECT returns the VALUE of the referenced cell, not just the reference, which is what i needed. and it seemed like such a breakthrough...i just can't see how it could work. any further suggestions??

 

[xlbo]

Ok - I think I get this - I don't think you need indirect as it returns the VALUE in the range you are referencing
Try this
.Formula = "=INDEX(" & Range("A2&quot.Text & "!D5:K25," & attvelrow & ",1)"
Rgds
~Geoff~

 

[chrishooper]

i think i really need to find a excel function to do what i need instead of having subroutines to changes the formulas.

so far i have had a play around with CONCATENATE but i can't get a function such as INDEX to accept a text string as an argument (unless typing in directly !??! :S)

eg. if cell a2 contains "IAC S"
cell b2 contains !D5:K25

if i type index = (concatenate(a2,b2),1,1)

shouldn't this produce the formula:

index = ("IAC S"!D5:K25,1,1)

at the moment i just get a value# error in the cell.

What am i doing wrong???

 

[chrishooper]

Right well thanks for your help so far guys, i'm a least at little way towards solving the problem the way i want to. If anyway has any further ideas about doing the job using an excel formula referring to a cell containing the worksheet name please don't hesitate to pen your thoughts.

Otherwise it's back to the cell-writing subroutines (do-able but not ideal).

thanks again

Chris

 

[RobBroekhuis]

Chris,
Wait, you're almost there! Any reference in a formula to a single cell value you can convert easily to an INDIRECT function call, for example:

=A1*B1

would become

=indirect(concatenate(A2,"!A1&quot)*indirect(concatenate(A2,"!B1&quot)

(if your sheet name is in A2)

You need to do something a little different with your INDEX function, but that too can be done in worksheet functions rather than VBA.
For example,

=indirect(concatenate(A2,"!D",A3+4))

(where A3 has your row#) to do the same as

=INDEX(sheet1!D5:K25,A3,1)

Does that make sense?
Rob

 

[chrishooper]

Right here we go again. Thanks for everybody's help so far, by the way.

I've tried the simple INDIRECT function example to replace the simple A1*B1 calculation. However when i delve into what's going on it seems that the output of the CONCATENATE function is given as a text string and is not readily recognised by the INDIRECT function.

I have typed the formulas exactly as written and have placed my sheet name into cell A2 as "Sheet 1" (no inverted commas).

 

[RobBroekhuis]

The nested indirect(concatenate(...)) structure works just fine. Note that the default sheet name assigned by Excel is "Sheet1" (no space). If you do have spaces in your sheet names, just add the single apostrophes, as in:

=indirect(concatenate("'",a2,"'!","A1&quot)

That should work.
Rob

 

[chrishooper]

Got it! Thank you so much for your help, everybody. Special thanks to tmktech for that flash of inspiration.

chris