Runtime Error 9 when another user runs my macro.

mth0012

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I've recently started training a coworker using macros that exist as a package (the macro file itself, a template, and reports that get generated based on the template).
I copy all relevant files to this user's desktop (which is where I put my copy) and rename every instance where the code referenced a C: location to match this user's C: filepaths.

Thinking that all is well, I give the macro some user inputs (Mold and Lot, shown below) and run.
I get a Runtime Error 9: subscript out of range.

VBA Code:
Mold = Workbooks("SQL Query").Worksheets("SQL Data").Range("A5").Value
Lot = Workbooks("SQL Query").Worksheets("SQL Data").Range("A3").Value

It seems that the macro can't reference a range even in the same workbook.
I've checked spelling, and switched the order of the Mold and Lot - both orders behave the same. Whichever line comes first gets the Runtime Error 9.
When I run this macro on my account, even the same computer, it works fine.
Is there any reason why a macro wouldn't work for another user even if everything seems to be the same?
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You should always include the file extension as part of the workbook name.
I suspect that you have the folder settings to "Hide known file extensions" & the other user doesn't.
 
Solution

mth0012

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
This was exactly my problem.
I specified the file extension of ever Workbook and now it works perfectly.
Thank you very much! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,679
Members
415,921
Latest member
ExcelNoob28

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