CODEWITHSUNDEEP
excelvbacellformula
Ryuu
Ryuu

Reputation: 606

VBA setting the formula for a cell

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.

Upvotes: 11

Views: 176521

Answers (4)

Eneas Gesing
Eneas Gesing

Reputation: 533

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

Cells(1, 1).FormulaLocal

Upvotes: 3

Carnifex
Carnifex

Reputation: 531

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.

Upvotes: 0

Stewbob
Stewbob

Reputation: 16899

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.

Upvotes: 22

David Zemens
David Zemens

Reputation: 53663

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.

Upvotes: 4

Related Questions