VBA sets formula for cell

Question

I am trying to set a formula for a cell using the name of a dynamically generated worksheet and a fixed cell address. I am using the following line but cannot make it work:

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

Any advice on why this does not work, or the product in the right direction, is welcome.

Thank you in advance

Answer 1

Not sure what doesn't work in your case, but the following code will put the formula in cell A1, which will retrieve the value in cell G2.

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

At the time this formula is placed, the workbook and worksheet must contain strProjectName references. Excel will immediately try to evaluate the formula. You may be able to stop this by disabling automatic recounting until the book exists.

Answer 2

Try:

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

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

If this does not solve the problem, provide additional information about the specific error or error.

Update

In the comments, you indicate that you are replacing spaces with underscores. Perhaps you are doing something like:

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

But if you do not push this change to the Worksheet.Name property, you can expect this to happen:

• A file view dialog box appears.
• Formula returns #REF error

The reason that you are passing a link to a sheet that does not exist is why you get the #REF error. The file dialog box is an attempt to allow you to fix this link by specifying a file that contains the sheet name . When you cancel, 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 you want to make the address directly, a worksheet must exist.

Disabling automatic recalculation will help you :)

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

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

The pasted formula will return #REF because the strProjectName page does not exist.

But after the appearance of this table, Excel will again calculate the formula and the correct value will be shown.
Disadvantage: there will be no tracking, therefore, if you move the cell or change the name of the worksheet, the formula will not be adjusted to the changes, as in direct addressing.

Answer 4

If cells (1, 1) .Formula gives error 1004, as in my case, it changes it to:

 Cells(1, 1).FormulaLocal