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

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,547
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
FYI, as an example, the first 4 lines of your code can be replaced with this one:

Code:
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

StevenGu3

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
FYI, as an example, the first 4 lines of your code can be replaced with this one:

Code:
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

New Member
Joined
Jul 13, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
FYI, as an example, the first 4 lines of your code can be replaced with this one:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,163
Messages
5,629,060
Members
416,363
Latest member
zaveedd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top