Back to original file

B

brook6

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
 
T

Tom Ogilvy

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 said:
I have a macro that opens a file to use for VLOOKUP function. I start in
Click to expand...
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
Click to expand...
file/worksheet, so I can get back to it before I execute the formula.
 
T

Tom Ogilvy

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 said:
This did get me back to original sheet, but I got 'application-defined or
Click to expand...
object-defined' error at the sh.Range line
 
B

brook6

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 said:
I have a macro that opens a file to use for VLOOKUP function. I start i
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
file/worksheet, so I can get back to it before I execute the formula
 
Top