Cell reference in formula misinterpreted by Excel...

[SunGodly]

Okay, put on your thinking caps. I am entering a formula via VBA containing a reference to another worksheet in the same workbook. Excel, however, seems to think I am looking for a file(?) and starts the 'Open File' dialog box when I run the macro:

UpdatePfmla1 = "=('RC-" & PrevRCNum & "'!CM249 - 'RC-" & PrevRCNum & "'!CG249)"
UpdatePfmla2 = "=('RC-" & PrevRCNum & "'!CM250 - 'RC-" & PrevRCNum & "'!CG250)"
Sheets("RC-" & NextRCNum).Range("CI249&quot.Formula = UpdatePfmla1
Sheets("RC-" & NextRCNum).Range("CI250&quot.Formula = UpdatePfmla2

The (variable) sheet I am referencing is named RC-xx (where xx is a two-digit number).

Now for the fun part: I am using the very same code elsewhere in this project without a problem... If you have any ideas, please let me know!

 

[cheerio]

Where it works do you have the sheet names beginning with RC?

There is an alternative cell naming convention called R1C1 and your sheet names just might look like cell references to Excel and confuse it. Perhaps you could try with sheet names beginning XYZ.

Ken

 

[SunGodly]

The code is exactly the same in the other routine, as odd as that sounds. Unfortunately, I have to stick with the naming convention that's already in use despite the similarity to the R1C1 reference. Any other ideas?

 

[bryanbayfield]

Hmmm ... odd one.

I certainly can't tell what the problem is from the snippet of code you've posted. If it's getting the code confused with

application.getopenfilename

you've got one deep-lying problem! Perhaps it'd be an idea to step through the macro with all the variables stuck in the watch window?

Personally though, what I'd do is already have the formulae set up in the workbook then have the following code:

For Each sh In ThisWorkbook.Sheets
    sh.Calculate
    Next

Surely this has got to be easier?

Bryan :

 

[SunGodly]

Bryan, thanks for the response. The problem is that the formula can't be static, it will have to reference a different worksheet occasionally, so I can't just keep the formula in the worksheet. The strangest thing is that I copied and pasted this code from another subroutine where it works without a hitch.

 

[bryanbayfield]

Is the subroutine in a different workbook?

Have you tried creating a new workbook, copying your code that doesn't work into it and seeing what happens?

Sounds like a right old nightmare this.

 

[cheerio]

The VBA editor can corrupt a module causing unexpected behaviour.

Try:

Copy and paste the module to notepad.
Delete the module
Compact the database
Recreate the module
Repaste in the code from Notepad

If the code is behind a form, before you compact set the HasModule property to false.

This may explain why the same code shows different behaviour.

Ken