How to get round #REF during macro column deletes

[Igwiz]

Hi,

I have a SUMIF formula in a worksheet1 that references a worksheet2. I then run a macro that moves and deletes all sorts of columns but eventually leaves it in exactly the same format it started in albeit with different data. However, the SUMIF formula in ws1 now has #REF errors where it is referencing columns that were 'deleted'. How on earth can I get round this. Don't want to have to redefine the formula every time as there are more than one and that seems very inefficient!

Cheers,
Ig

 

[SkipVought]

Hi Igwiz,

What do you want to happen to the formula if the column is deleted.

The data that #REF was referring to is no longer there.

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[DPlank]

I'd suggest you copy out the formula to a variable and replace it in the workbook once the column fiddling has been completed.

Not too hard to do using copy and paste, even in a macro.

HTH
David

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[Igwiz]

Skip,

Like I said, it only gets deleted because I copy over new data, consolidate and then put it back.

DPlank,

Ideally I want to avoid that because I have several that need replacing and it loops hundreds of times.

Will turning off automatic calculation when I'm doing the jiggery-pokery work?

Thanks.

 

[SkipVought]

You ought to modify your macro to NOT go deleting columns if the format is going to be the same. Just ClearContents instead of Delete.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

BTW, you'll also have to NOT Insert -- rather Copy to those columns

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Igwiz]

Have rewritten the macro. Thanks.