I have a macro that opens a file to use for VLOOKUP function. I start in original file, ask user for file to use and I want to execute the function in my original file, not the file I opened

fName = Application.GetOpenFilenam
Workbooks.Open fNam

Range("N2").Selec
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-13],'[fName]Qty-Value'!$A:$C,2,FALSE)

I think I need something before this to capture the name of the original file/worksheet, so I can get back to it before I execute the formula

Dim sh as Worksheet
set sh = ActiveSheet
fName = Application.GetOpenFilename
Workbooks.Open fName
sh.parent.Activate
sh.Activate

sh.Range("N2") _
.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[fName]Qty-Value'!$A:$C,2,FALSE)"

--
Regards,
Tom Ogilvy


"brook6" wrote in message
...
I have a macro that opens a file to use for VLOOKUP function. I start in
original file, ask user for file to use and I want to execute the function
in my original file, not the file I opened.

fName = Application.GetOpenFilename
Workbooks.Open fName

Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-13],'[fName]Qty-Value'!$A:$C,2,FALSE)"

I think I need something before this to capture the name of the original
file/worksheet, so I can get back to it before I execute the formula.

I don't doubt it - that is your formula and it appears screwed up. You have
A1 style references (but pass it to FormulaR1C1), in fact you have a mix of
both reference styles, probably a non-existent name for the workbook in the
formula. But you intimated it worked, so I didn't even look at that.

Sh.Range("N2").FormulaR1C1 =

Part is fine and shouldn't cause an error.

--
Regards,
Tom Ogilvy

"brook6" wrote in message
...
This did get me back to original sheet, but I got 'application-defined or
object-defined' error at the sh.Range line

thx

This formula worked, prior to inserting in this macro...cleaned up the RC stuf
I am getting 'application-defined or object-defined' error on this line..

ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-13],'[fName]Qty-Value'!$c1:$c3,2,FALSE)

I think I may have filename/worksheet syntax issue. Qty-Value is a valid worksheet in the file fName. Not sure on quotes/brackets/space


----- Tom Ogilvy wrote: ----

Dim sh as Workshee
set sh = ActiveShee
fName = Application.GetOpenFilenam
Workbooks.Open fNam
sh.parent.Activat
sh.Activat

sh.Range("N2")
.FormulaR1C1 =
"=VLOOKUP(RC[-13],'[fName]Qty-Value'!$A:$C,2,FALSE)

--
Regards
Tom Ogilv


"brook6" wrote in messag
..
I have a macro that opens a file to use for VLOOKUP function. I start i
original file, ask user for file to use and I want to execute the functio
in my original file, not the file I opened
fName = Application.GetOpenFilenam
Workbooks.Open fNam
Range("N2").Selec
ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-13],'[fName]Qty-Value'!$A:$C,2,FALSE)
I think I need something before this to capture the name of the origina
file/worksheet, so I can get back to it before I execute the formula