Excel Macro Error -- "Subscript out of Range error 9&qu

Jim1

New Member
Joined
Jan 22, 2004
Messages
5
Background -- For nearly one year I have been executing a macro which opens various files, goes to the worksheet CY-PY USD, updates a cell, saves each file and closes each file. Following is the specific code executed:

Workbooks.Open FileName:= _
"\\Fin Color Group\Forecast Pkg\21.xls", UpdateLinks:=3
Workbooks("21").Worksheets("CY-PY USD").Activate
Application.Goto Reference:="R5C4"
Dim Period_Code As Range
Set Period_Code = Worksheets("CY-PY USD").Range("D5")
Period_Code.Formula = VPeriod
Calculate
ActiveWorkbook.Save
ActiveWorkbook.Close

File Location -- Files reside on a Citrix server based at my office location, with the capability to access the drive and execute macros directly from the office or via internet dial-in.

Issue -- Our e-mail is Lotus Notes (another story). Two weeks ago my PC was upgraded to a new version of Notes. Ever since the upgrade, I get the "Subscript out of Range error 9" message when the macro attempts to execute the code:

Workbooks("21").Worksheets("CY-PY USD").Activate

This error occurs when I am accessing the server directly from my PC while in the office and full up the file on my PC.

While in the office, when I access the Citrix server via the internet and run the marco on the Citrix server, everything executes okay.

Any ideas???

Jim
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Re: Excel Macro Error -- "Subscript out of Range error

Jim,

The only way you should see that error is if you fail to open the workbook. When you are in the environment where your macro is failing, can you open that WB manually? If so, if you type ? activeworkbook.path in the immediate window, does the path correspond to what you're specifying in your code?

HTH
 
Upvote 0
Re: Excel Macro Error -- "Subscript out of Range error

Greg

When I execute the macro it actually does open the file 21.xls in Excel, then it stops on the code to find the CY-PY USD worksheet.

Jim
 
Upvote 0
Re: Excel Macro Error -- "Subscript out of Range error

Hmmm, try putting a ".xls" at the end of the WB name [Workbooks("21.xls").~]. Otherwise, I'm stumped. Only other subscript is your WS subscript, so unless a space or something crept into your WS name to make the name inside your worksheets() reference invalid (and you'd also have to be opening different copies of the same WB for this to fail one time and not fail another).
 
Upvote 0
Re: Excel Macro Error -- "Subscript out of Range error

Greg .....

Blow me away, it worked!!!!!

Added the .xls and tried it via direct access to the server as well as access via the internet. Both were successful.

Still can't understand why this happens, but ya cracked the problem.

Thanks again

Jim
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top