How can I use values in a formula in VBA 1

[Naomi72]

I'm trying to automate some excel procedures using vba. Currently I'm having difficulties when I need to insert a formula into a cell that takes values such as the row and the sheet name in a file, and passes then into the formula.
SheetName is entered in a form.

MyRow = Selection.Row
Formula1 = "=LOOKUP($A MyRow,'Locationoffile\[Filename]SheetName'!R8C1:R33C1,'Locationoffile\[Filename]SheetName'!R8C5:R33C5)"

Any help, hints etc., would be brilliant.

Thanks

 

[ETID]

Maybe as an alternate....


you could pass the result of the formula directly to the cell



sheets("my_sheet&quot.range("my_cell&quot=application.worksheetfunction.LOOKUP($A MyRow,'Locationoffile\[Filename]SheetName'!R8C1:R33C1,'Locationoffile\[Filename]SheetName'!R8C5:R33C5)

 

[xlbo]

Formula is entered as a string so:

Formula1 = "=LOOKUP($A" & MyRow & ",'Locationoffile\[Filename]SheetName'!R8C1:R33C1,'Locationoffile\[Filename]SheetName'!R8C5:R33C5)"

should work


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Naomi72]

Thanks a million Geoff, that worked fantastically.

Kind Regards

Naomi