Save formulas and repaste

[doubledaffy]

I have a rather unique problem with my macro. It is too extensive to post here so I'll try to explain it as clearly as I can.

The Setup: I have a bunch of formulas in row 3. The number can change, so the last column to the right is not static. The formulas refer to cells on another sheet, that can get deleted. When they get deleted I get the #Ref! warning. To avoid this I save the formulas using

Eqx=Worksheets("Virtual").Range(Cells(3,1),Cells(3,LastCol)).Formulas

The variable "LastCol" refers to the last column which as I mentioned before is dynamic. Later when I'm done doing all of the deleting, I fill the cells in again with their previously stored formulas. I just swap that previous equation around.

The Problem: It doesn't work. Whenever I execute the code it gives me the classic Runtime error.

The Super Problem: When I go to debug it and just step through it, it works fine.

Any ideas what the problem might be? I can try to provide more details if you need them.

 

[drsarao]

Runtime error is most likely due to last "s". It should be .Formula

Eqx=Worksheets("Virtual").Range(Cells(3,1),Cells(3,LastCol)).Formula

Though I can't think of any reason, why it should work in step mode!

 

[doubledaffy]

Sorry that was just a typo. There is no extra "s" in the real code. Thanks for the reply though.

 

[doubledaffy]

Ok, I've figured it out! Oddly enough, the worksheet had to be activated first to avoid the problem. No idea why...

 

[drsarao]

I agree.
Some of the Excel code which should work logically, doesn't (or works erratically), till you activate the sheet. A bug?
In fact this is the case where code doesn't work but step thru does.
Ideally speaking, VBA should not care as to which sheet is active because it is acting on the objects directly.
Comments are invited from MVPs.
Thanks.