I'm trying to set the formula for a cell using a (dynamically created) sheet name and a fixed cell address. I'm using the following line but can't seem to get it working:
"=" & strProjectName & "!" & Cells(2, 7).Address
Any advice on why this isn't working or a prod in the right direction would be greatly appreciated.
See Question&Answers more detail:os
Not sure what isn't working in your case, but the following code will put a formula into cell A1 that will retrieve the value in the cell G2.
strProjectName = "Sheet1"
Cells(1, 1).Formula = "=" & strProjectName & "!" & Cells(2, 7).Address
The workbook and worksheet that strProjectName references must exist at the time that this formula is placed. Excel will immediately try to evaluate the formula. You might be able to stop that from happening by turning off automatic recalculation until the workbook does exist.
