| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Back to original file
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Back to original file
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Back to original file
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Going back to original location | Excel Discussion (Misc queries) | |||
| Can I undo a "save file" to revert back to original spreadsheet | Excel Discussion (Misc queries) | |||
| Auto save replaced my original file and now I need the original? | Excel Discussion (Misc queries) | |||
| How to revert back to the original file name in a hyperlink | Excel Discussion (Misc queries) | |||
| using VBA to go back to the original value before the cell was changed | Excel Programming | |||
Hybrid Mode
