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.

Solution 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.

Solution 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.

Solution 3

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

Cells(1, 1).FormulaLocal

Comments

[Ryuu]

I handle the space issue by replacing spaces with underscores. There's no error per se, it just doesn't resolve the formula (shows #REF!) If it's any help, whenever I run the macro; it opens up a file browse dialogue box after passing that point in the code

[Ryuu]

That's what I've been doing but it doesn't resolve the formula - just shows #REF! If it's any help, whenever I run the macro; it opens up a file browse dialogue box after passing that point in the code

[David Zemens]

If you're not also pushing the REPLACE function to the Worksheet.Name property, that is exactly what I'd expect to happen. See revision above.

[David Zemens]

@Stewbob's comments above point you to the root cause of the error. Place the formula after the sheet has been created. then you should be all set.

[Ryuu]

Thank you for your help. It was just ordering. Rookie mistake but I've only been doing anything with macros for 2 days. Kind of expected that was the answer but was hoping it wasn't...

[SeanC]

@Ryuu, if it's asking for a filename, then the strProjectName is not pointing to a valid sheet name. A full reference for a formula is [FileName]Sheetname!Address. You can omit [Filename] for the current file, and you can omit [Filename]Sheetname for the current sheet, but those are the only combinations allowable in a cell reference

[pashute]

@Ryuu If your project name has a space in it you have to add a single quotemark before and after the project name.