VBA setting the formula for a cell

9
votes

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.

4
excelvbacellformula
I didn't try your formula, but as alternative you can always create a change event in a worksheet module and make the calculation programmatically. - html_programmer
What is an example value for strProjectName? Are you trying to reference a worksheet or workbook that doesn't exist? - Stewbob
At that point it doesn't exist but a few lines later the sheet is created. Wouldn't think this would be an issue as when the macro finishes running; the sheet is in place - Ryuu
@Ryuu, that is your issue. When you place that formula, Excel immediately tries to reference it. When it can't find the workbook, it opens up the file browser so that you can pick a valid workbook. - Stewbob
@Stewbob, I was hoping that wasn't the problem. I'll try that now and get back to you. - Ryuu

4 Answers

19
votes

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.

4
votes

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.

1
votes

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

Cells(1, 1).FormulaLocal
0
votes

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.