Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Worksheet name as a variable in VBA
Hello,
I am trying to use a cell value (C2 in the worksheet "Test") to dictate the worksheet I want to reference from in my if formula. In other words, all the '21-May' in my formula should be replaced by the cell value (C2) that refers to another worksheet. I replaced '21-May' with strName but it did not work. Will appreciate any help. Thank you. Dim strName As String strName = ActiveWorkbook.Sheets("Test").Range("C2").Value Range("B36").Select ActiveCell.FormulaR1C1 = _ "=IF(AND(R[-4]C[34]=0,R[-4]C[36]=0),0,(SUMIFS('21-May'!C21,'21-May'!C8,""CSA"",'21-May'!C9,""Late"")+SUMIFS('21-May'!C21,'21-May'!C8,""CSA"",'21-May'!C9,""Exception""))/(R[-4]C[34]+R[-4]C[36]))" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name as a variable in VBA
When you replaced '21-May' with strName, did you include the apostrophes? In a formula the '21-May' is a reference to a tab, and the value in cell c2 likely does not include apostrophes. Excel rejects the formula written by VBA if the apostrophes are not present.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link variable data from one worksheet to another worksheet? | New Users to Excel | |||
Variable worksheet name (tab) linked to cell in same worksheet | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Use a variable for a worksheet name? | Excel Programming | |||
Assigning worksheet to worksheet variable | Excel Programming |