| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
refer to worksheet by index number in formula
Hi
I'm trying to have a Vlookup formula inserted by a macro, but if possibly I want to refer to the range by worksheet index number - the sheet will always be in the same spot, but won't always be called the same thing. The line is a simple one: ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'Sheet2'!R1C1:R100C19,11)". Is there a way to refer to Sheet2 by it's index number, or another way to set up the formula so it works? Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
refer to worksheet by index number in formula
Aaron,
As you are using a macro to build your formula string, you can get the name from the index ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'" & Worksheets(1).Name & "'!R1C1:R100C19,11)" NickHK "Aaron" ... Hi I'm trying to have a Vlookup formula inserted by a macro, but if possibly I want to refer to the range by worksheet index number - the sheet will always be in the same spot, but won't always be called the same thing. The line is a simple one: ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'Sheet2'!R1C1:R100C19,11)". Is there a way to refer to Sheet2 by it's index number, or another way to set up the formula so it works? Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
refer to worksheet by index number in formula
Thanks, Nick, worked perfectly.
"NickHK" wrote: Aaron, As you are using a macro to build your formula string, you can get the name from the index ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'" & Worksheets(1).Name & "'!R1C1:R100C19,11)" NickHK "Aaron" ... Hi I'm trying to have a Vlookup formula inserted by a macro, but if possibly I want to refer to the range by worksheet index number - the sheet will always be in the same spot, but won't always be called the same thing. The line is a simple one: ActiveCell.FormulaR1C1 = "=Vlookup(RC[-1],'Sheet2'!R1C1:R100C19,11)". Is there a way to refer to Sheet2 by it's index number, or another way to set up the formula so it works? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I refer to cell in another worksheet in a formula | Excel Worksheet Functions | |||
| Refer to the previous worksheet in a formula | Excel Worksheet Functions | |||
| Worksheet function to refer to columns by number | Excel Discussion (Misc queries) | |||
| Getting the index number by supplying worksheet name | Excel Programming | |||
| Formula to refer to other worksheet... | Excel Worksheet Functions | |||
Linear Mode
