excel - VBA setting the formula for a cell

Question

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.

Answer 1

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.