Worksheet name as a variable in VBA

[superwalkerlee]

Hello,

I am trying to use a cell value (C2 in the worksheet "Test") to dictat
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 bu
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]))

 

[Dave O]

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.