| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Diff Sheet in Formula in VBA
Right now I have a code line in my macro which says:
Range(CellLocation).Value = "=SUBTOTAL(9,'Main'!R1C5:R200C5)" I want to replace 'Main' with a variable name (SheetName) that represents a sheet name. I get errors if I if replace 'Main' with SheetName or 'SheetName'. Is it possible to do what I want? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Referencing Diff Sheet in Formula in VBA
maybe...
Range(CellLocation).FormulaR1C1 _ = "=SUBTOTAL(9,'" & yourVariableHere & "'!R1C5:R200C5)" If you used a variable to represent the worksheet (not the worksheet name): Range(CellLocation).FormulaR1C1 _ = "=SUBTOTAL(9,'" & yourWksVariable.Name & "'!R1C5:R200C5)" I also changed .value to .formular1c1. Al wrote: Right now I have a code line in my macro which says: Range(CellLocation).Value = "=SUBTOTAL(9,'Main'!R1C5:R200C5)" I want to replace 'Main' with a variable name (SheetName) that represents a sheet name. I get errors if I if replace 'Main' with SheetName or 'SheetName'. Is it possible to do what I want? -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| make formula look at diff sheet | Excel Discussion (Misc queries) | |||
| SUMIF formula required to search for 2 diff values in 2 diff colum | Excel Worksheet Functions | |||
| Problem w/formula referencing another sheet | Excel Worksheet Functions | |||
| copy formula referencing sheet name to another sheet | Excel Worksheet Functions | |||
| Referencing Sheet Names In Formula | Excel Programming | |||
Linear Mode
