runtime error 9 subscript out of range when activating a .csv file

[StevenGu3]

Hi everyone,

This is a new one on me, I open several files as part of a batch, process data via a macro then save the resulting file. For months this has been working fine, no issues, but in the past few days I have been getting a runtime error 9 when it hits the following line in VBA. None of the coding has been changed at all, it has been static for months.

One clue? If I take the .csv file which is being activated, remove it from the folder it resides in then place it back in the folder then repeat the task by reopening all files then running the macro again it does not fail at the line below, otherwise I get the error. But this is time consuming. Have seen the issue with other files also - but again only very recently. All the files which fail on activation are .csv file and all actioned smoothly until the past few days.

The line it fails with subscript out of range is:

Windows("Website Suppliers.csv").Activate

Any help welcome.

Thanks, Steven

 

[RoryA]

FYI, as an example, the first 4 lines of your code can be replaced with this one:

Workbooks("auto cost and rrp pricing v2.xlsm").sheets("Calculation").Rows("2:2").replace what:="=", Replacement:="^", Lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false

without any selecting or activating.

 

[StevenGu3]

FYI, as an example, the first 4 lines of your code can be replaced with this one:

Workbooks("auto cost and rrp pricing v2.xlsm").sheets("Calculation").Rows("2:2").replace what:="=", Replacement:="^", Lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false

without any selecting or activating.

Thanks Rory, will look towards rewriting if I cannot get it to work, of course when I tried to generate the error it went straight through and ran perfectly. Intermittent errors are the pits! My VBA level is basically down to creating macros and amending.

 

[StevenGu3]

FYI, as an example, the first 4 lines of your code can be replaced with this one:

Workbooks("auto cost and rrp pricing v2.xlsm").sheets("Calculation").Rows("2:2").replace what:="=", Replacement:="^", Lookat:=xlpart, searchorder:=xlbyrows, matchcase:=false, searchformat:=false, replaceformat:=false

without any selecting or activating.

Hi Rory,

The issue appears to be with this PC, so I will swap it out. No idea what the cause is but I am now getting many errors in different places. Excel will randomly stop reading data across workbooks but working on a single workbook is fine. I synced my laptop and transfered things over to it and the same VBA that did not work on the Desktop worked on the Laptop.

Thanks for your help.

-Steven