For Next Loop

I have some code which all runs perfectly. I needed to expand the code with a For Next Loop, and so added this, but when I run it the workbook just hangs. Have I set it into an endless loop and what do I need to do to prevent that?

I'm still very new to VBA and am adding code through trial and error but am unsure where I'm going wrong...

Added Code

Please Login or Register  to view this content.

Many thanks

See if setting calculation to manual makes any difference.

I don't use vba with charts so can't comment on specifics of your code, but standard excel behaviour with automatic calculation would be to recalculate every time the code passes over WorksheetFunction.

Stepping through the code with f8 and monitoring the values of your variables is sometimes the easiest way to find why your code is running slow or not as expected.

In your main code you have

Please Login or Register  to view this content.

which is used to disable a couple of things and make the code run more efficiently, another line you can use here is

Please Login or Register  to view this content.

Which turns of automatic calculation, then

Please Login or Register  to view this content.

which turns it back on again after the code has finished.

Also, if your code is ended prematurely (for example, by killing the process when it hangs or ending the code when it errors), these settings do not automatically re-enable, you need to do it manually. These are excel settings, not workbook settings, so closing without saving will not cancel out the original change to disable them. The simple way to do this is with a small piece of code like

Please Login or Register  to view this content.

Regarding the runtime error, does the workbook 'New Workbook Name.xlsx' actually exist? If that workbook is not open then you would need to specify the full workbook file path, not just the name.

See if this extract from PeltierTech (<<link to page) helps, it shows the correct way that the code should be written for what you're trying to do.

Please Login or Register  to view this content.

Hopefully this is of some use. I expect that Rory (romperstomper) will have significantly more knowledge and experience than I do relating to chart use and will (hopefully) provide further assistance.
Don't be afraid to ask if there is something you don't fully understand.

Strangely PeltierTech is where I 'borrowed' this bit of code from...

Please Login or Register  to view this content.

With regards your question 'does the workbook 'New Workbook Name.xlsx' actually exist?' I had been thinking about this, the answer is no because the code is designed to create a new workbook and save the file with the name of the new workbook but having said that, my understanding of the code above is that all it should be doing is changing the filename for the data series, as in from:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

before saving the file, so does 'New Workbook.xlsx actually need to exist before the file is saved? If the answer is yes, then does that mean the code needs to sit after

Please Login or Register  to view this content.

I'll experiment with some of your suggestions and let you know how I get on. Many thanks for all your very helpful suggestions. It feels as though the code is 95% there but I'm just missing one vital element... I just wish I know what that vital element was.

When you run the line to update the formula (chart series) excel will attempt to create and validate the link, if there is nothing there then you will get an error, so ideally you would save the workbook first to create the valid filename, then update the charts.

You might be able to make it work with something like

Please Login or Register  to view this content.

but without testing I'm not certain of that.

Personally, I would save first to create the new filename and path, then work with that.