run time error -2147417848 (80010108) Method 'Formula' on object 'Series' failed

[dluhut]

I had this particular code and it gives me an error.

Please Login or Register  to view this content.

As you can see, I have an if statement at the top to check if mySrs.Formula has the string that I want to substitute, and if not, then end if.

However, it seems that even though the InStr is giving the result of 0, it still kicks in to 'mySrs.Formula' line.

May I know what's wrong with it?

[Norie]

If InStr does return 0 the mySrs.Formula will definitely not be executed.

Have you checked what WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) is actually returning when you get the error?

PS VBA has it's own equivalent of WorksheetFunction.Substitute - Replace.

[dluhut]

Hi Norie,

That's what I thought too...if InStr does return 0, then mySrs.Formula will not be executed. However it DOES!!! Which is why I'm confused.

From what I can tell, the mySrs.Formula = WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) is giving the error because mySrs.Formula does not have the 'Old String' that I want to replace. That's why it's giving me the error.

That's the reason why I have the IF statement to check if mySrs.Formula has 'Old String'.

No matter how I run the macro multiple times, it's giving me the same error line.

I tried to use 'On error go to next', but some how, all the charts that were copied over (via copy and paste to new destination workbook) were all gone.

If InStr does return 0 the mySrs.Formula will definitely not be executed.

Have you checked what WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) is actually returning when you get the error?

PS VBA has it's own equivalent of WorksheetFunction.Substitute - Replace.

[Norie]

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[dluhut]

I'd definitely would upload samples if I could. But I really can't.

Because it's 2 files...and these 2 files, they have several sheets (identical sheet name in each) that I need to copy from and to.

But before I copy over, I need to delete first.

So basically, what I did is that when I run the macro:
1. Macro will pop up a user form box to ask user which is the SOURCE WORKBOOK that I want to copy the sheets from.
2. Macro will pop up a user form box to ask user which is the DESTINATION WORKBOOK that I want to paste the sheets to.
3. Macro then delete all those sheets from the destination workbook.
4. Macro then copy each desired sheets from the source to the destination.
5. Macro then change all cells and charts that has formula of the source workbook name (OldString) to the current destination workbook (NewString)

Any chance you could upload a sample workbook?

Click on GO ADVANCED, scroll down and click Manage Attachments.

[Norie]

Have you checked what WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) is returning when you get the error?

To do that just click Debug when you get the error, open the Immediate Window with CTRL+G and enter this in it.

Please Login or Register  to view this content.

[dluhut]

Please Login or Register  to view this content.

is returning

Please Login or Register  to view this content.

Also I've attached a picture on my 1st thread to show the values of the Instr etc.

Have you checked what WorksheetFunction.Substitute(mySrs.Formula, OldString, NewString) is returning when you get the error?

To do that just click Debug when you get the error, open the Immediate Window with CTRL+G and enter this in it.

Please Login or Register  to view this content.

[Norie]

Is that formula right?

What happens if you change the code to this?

Please Login or Register  to view this content.

[dluhut]

It's still giving the same error.

It still goes into the mySrs.Formula (below the IF statement) even though the Instr is giving 0 as the answer.

Is that formula right?

What happens if you change the code to this?

Please Login or Register  to view this content.

[Norie]

There must be something wrong with the formula/series or something else.

What, in words, is the code meant to do anyway?

[dluhut]

Hi Norie,

Problem solved. I moved from

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

And it works. So I guess all is good.

Reps up to you for helping me out here.

[dluhut]

What I was trying to do is to loop through all sheets, and for each sheet that has a chart, look at the formula (which is the mySrs.Formula), and if the formula string has the 'Old String', replace it with the 'New String'

What, in words, is the code meant to do anyway?