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.

Thanks in advance

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.

Answer 2

Try:

.Formula = "='" & strProjectName & "'!" & Cells(2, 7).Address

If your worksheet name (strProjectName) has spaces, you need to include the single quotes in the formula string.

If this does not resolve it, please provide more information about the specific error or failure.

Update

In comments you indicate you're replacing spaces with underscores. Perhaps you are doing something like:

strProjectName = Replace(strProjectName," ", "_")

But if you're not also pushing that change to the Worksheet.Name property, you can expect these to happen:

1. The file browse dialog appears
2. The formula returns #REF error

The reason for both is that you are passing a reference to a worksheet that doesn't exist, which is why you get the #REF error. The file dialog is an attempt to let you correct that reference, by pointing to a file wherein that sheet name does exist. When you cancel out, the #REF error is expected.

So you need to do:

Worksheets(strProjectName).Name = Replace(strProjectName," ", "_")
strProjectName = Replace(strProjectName," ", "_")

Then, your formula should work.

Answer 3

If Cells(1, 1).Formula gives a 1004 error, like in my case, changes it to:

Cells(1, 1).FormulaLocal

Answer 4

If you want to make address directly, the worksheet must exist.

Turning off automatic recalculation want help you :)

But... you can get value indirectly...

.FormulaR1C1 = "=INDIRECT(ADDRESS(2,7,1,0,""" & strProjectName & """),FALSE)"

At the time formula is inserted it will return #REF error, because strProjectName sheet does not exist.

But after this worksheet appear Excel will calculate formula again and proper value will be shown.
Disadvantage: there will be no tracking, so if you move the cell or change worksheet name, the formula will not adjust to the changes as in the direct addressing.