Close Window
Professional Excel Help for Businesses
Remote, on-demand, Excel consulting and help desk.

Run-time Error 9: Subscript Out Of Range

I am getting this error when macro is run on open ... anyone have any ideas how to get rid of it ???

Run-time error '9': Subscript out of range


Windows("JobCodeMaster.xls").Visible = False

- Ask any question about Excel and have it answered in no time.

Similar Excel Tutorials

How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...

How to remove the #N/A error from Vlookup and replace it with a friendly message or a blank cell. Fix the Vlookup ...

How to count the number of errors in a range in Excel; also, how to count the occurrence of a specific error. Sect ...

How to prevent errors from appearing in formulas in Excel. This is especially helpful for the Vlookup function. Sec ...

Helpful Excel Macros

- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the

- Automatically run an Excel macro at a certain time. This allows you to not have to worry about running a macro every da

- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai

- This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from

- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst

Similar Topics

How can i solve a debugger error that states "run-time error '9'...Subscript out of range.

for a search box code provided by Joe Was

I am looking for the code to select the visible data after applying a data filter. Actually I know how to select the data after applying the data filter but the issue is I am not able to exclude the header row and give the target range as used (non-blank) rows only!!

I am using below code to Select the Visible rows in the target range:



Problems in this code a

1) after applying the filter, while selecting the data it is selecting all the rows in given range till last row on the workbook. I need this to select the the data only till the last used row in the given range.

2) It is not possible to provide the address of the first row after we apply the filter since the first row address may change depending on the values in the table.

E.g. 1st time when I am running the macro the first row in the visible filtered data is starting at Cell address A4 and next time when I will run the macro it may be A6

3) The Code is also selecting the 1st row which is a header row. How can we exclude it from selection.

Some one please revert with the solution.
Thanks in advance.

Is there an on error exit sub command. I would like my Macro to just stop running if there is an error instead of an error message popping up. Thanks in advance

Hi all,

I've been getting this error on occasion recently.

- It's a shared document
- There is usually 10-12 people using the file at the same time

I've read on the Microsoft Help site that the issue is that somebody is accessing the file while another is trying to open it.

Is there a way, perhaps through VBA, that I can not allow a user to save while somebody else is opening the document? Other suggestions on how to avoid this error?


Good day... I need an IF Function that will allow me to action a time in a time range:

... If the time 04:16 falls in the time range 04:00 - 04:29, than put a one (1) in the filed x...
... If the time 04:16 doesn't fall in the time range 04:00 - 04:29, than leave the x fiel empty

Any help is appreciated.

I'm trying to open a pdf file from within excel vba. I have tried using the followhyperlink method but adobe acrobat opens very briefly then immediately closes Code:

Sub OpenPDF()
'Dim pdf As String
 On Error Resume Next
 'pdf file to open
 pdf = "K:\PDF\mypdf.pdf"
 'open the pdf file
 ActiveWorkbook.FollowHyperlink pdf
End Sub

So then I tried to create an instance of acrobat by setting a reference to the acrobat object but I can't get this to work either!

The code I'm using is Code:

Sub OpenPDF()
 Dim pdf As AcroPDDoc
 Dim strPDF As String
 Set pdf = CreateObject("AcroExch.PDDoc")
 'pdf file to open
 strPDF = "K:\PDF\mypdf.pdf"
 'open the pdf file
 pdf.Open strPDF
End Sub

Any ideas what could be wrong with either approach?


Is it possible to share a workbook with macros and have the macros work properly. I have one spreadsheet that needs to be shared by many users. We want to all be in the same document at the same time saving changes. The real catch is that the workbook has macros. I used the 'Share Workbook' function under tools. At the end of the setup, it told me that the macros would not work properly. The odd thing is that everytime I try to click on one of the macros I get an error, but yet the macro still performs the function. So essentially the macro works, it is just a pain because you have to click 'end' everytime on the error screen. Any suggestions?


I get the error message "reference is not valid" each time I open my spreadsheet. I get this message three times, and once I am done clicking ok on all three of them, my spreadsheet works just fine. The problem is, I have to send it to a bunch people.

I have a sheet with raw data a sheet with pivot tables a sheet with a dashboard and a simple macro
I don't have any #REF cells either.

Someone has any idea of what could be the problem?



I am trying to insert a range of cell in the body of an outlook email with the same format. The code I am using now is below and it does insert anything in the email body.


Sub Mail()
Dim OutApp As Object
 Dim OutMail As Object
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 On Error Resume Next
 With OutMail
 .To = ""
 .CC = ""
 .BCC = ""
 .Subject = "Burden Report"
 .Body = ActiveSheet.Range("A1:D12")
 .Attachments.Add ("C:\Documents and Settings\cbelcher\Desktop\Burden Report.xls")
End With
 On Error GoTo 0
 Set OutMail = Nothing
 Set OutApp = Nothing
 Set objMsg = Nothing
End Sub

I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data.

When I try to replace all commas (with nothing or with another character), I get the error message "The formula you typed contains an error."

I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.

Corporate edict.

I have a worksheet that is locked and protected now, except for cells in a certain collumn. I have named the cells in that column "MS96A".

If a user enters a date in a cell or range of cells anywhere in the column, the changed cells also need to be locked and protected (Once they enter a date, it is not allowed EVER to be changed again. Corporate requirement! *Shrug*).

What I am looking for is this. If the user selects that cell again, they will get the usual pop-up message, "The cell or chart that you are trying to change is protected..."

I think I am close, but I am getting an "End If without block If" error on the If Clause.

Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MRange As Range
Set MRange = Range("MS96A")
' If Not Intersect(Target, MRange) Is Nothing Then For Each cell In MRange Sheets("Sheet1").Unprotect Password:="temp"
cell.Interior.ColorIndex = 3
cell.Font.Color = vbBlack
Selection.Locked = True
Selection.FormulaHidden = False
Next cell
ActiveSheet.Protect Password:="temp", _
DrawingObjects:=False, _
Contents:=True, _
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Hello -

I am using Excel 2007. In prior versions of excel, when copying data, if I wanted to copy visible cells only, I would select "Go To, Special, Visible Cells Only" and then copy a range a cells. The default was always set to copy everything (including hidden cells), unless I specifically selected copy visible cells only.

In 2007, the default is somehow set to always copy only visible cells. Sometimes, I want to be able to copy all cells including those hidden but cannot seem to figure out how to swith this default option. I looked under Excel Options and did not see an option there.

I don't want to have to unhide and rehide everything each time i copy. I know i could do the hiding and unhiding via VBA but would prefer not to have to.

Thanks for your suggestions.


I have a number of different files that I often need to run a macro on. In order for me to do it on the 75-100 files I have at any given time, I need to open one, run the macro, close and save, then open the next one.

Is it possible to write a macro that will start with the first file in a folder, open it and update links, run a macro, save and close, and open the next file in the folder until it has open all the files in the folder.

I have experience with creating macros that reference different workbooks, but not sure how to go about opening files with different filenames (without referencing the exact filename).

I'd like to be able to have basic code for opening, saving and closing, opening next file, saving and closing, etc. and input the macro I'd need to run in each file in the appropriate location. Is this possible? Any help is greatly appreciated!!



As the title goes this is as simple as it gets. The macro worked good when I was using excel 2003 but now that I have 2007 everything works for except the resizing (Picture.Width & Picture. Height).


Sub AddImage()
Dim strPath As String
Dim strFile As String
Dim Picture As Object
Dim Response As String
Dim C As Range
strPath = "C:\\Photos\"
For Each C In Range("A1", Range("A65536").End(xlUp))
 If C 0 Then
 With C.Offset(0, 1)
 Set Picture = Nothing
 On Error Resume Next
 Set Picture = Sheets("Raw Pix").Pictures.Insert(strPath & C.Value & ".jpg")
 Picture.Top = .Top
 Picture.Left = .Left
 Picture.LockAspectRatio = msoTrue
 Picture.Width = 157
 Picture.Height = 138

 End With
 End If
Next C
End Sub

I have tried to record some macros while I am resizing the pictures but to no avail the macros are blank.

Please help


I am trying to figure out how to write a formula to figure out production cycle time.
We will be building 8 "widgets" a day.
We will work 10 hours a day.
There is a 20 minute break at 9:20 am.
Production shuts down for lunch 30 minutes for lunch at 12:30.
Production starts at 6:00 am.
Here is what I can do.
Production cycle time = (10*60)-30/8 or 71.25 minutes
If we start at 6:00 am, the first scheduled cycle completion time = TIME(6,0,0+71.25/1440)

My problem is when I am trying to use a logical "IF' statement to account for the 20 minute break or lunch and still calculate the end of each cycle time through the day I receive a number of error messages.
This is the formula I am trying to make work. I have the cell the formula is in, formatted with a "mm,ss" format.
I have included an attachment to help (a picture is worth a thousand words).

Any help will be greatly appreciated.
I have bought a number of books in an attempt to figure this out, and I am still stumped

Hi all,

I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise.

I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer.

Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly.

It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly.

I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel.

I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware.

I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out!

Thanks very much for your time.

Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.

 ActiveWindow.Zoom = True

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?

Hi All,

I got the below macro which uses IE and open the URLs. I want to create a userform with listbox with radio button and commandbutton on the same which will help me to connect to each url when I select the same in listbox and click on the commandbutton.

Sub DoBrowse1()
 Dim ie As Object
 Set ie = CreateObject("Internetexplorer.Application")
 ie.Visible = True
 ie.Navigate ""
End Sub

Any suggestions..

I am trying to get my macro to do this on open. Check if a1 is empty, if its empty run another macro (which is an input box to put something IN a1), but if A1 has something in it, DO NOTHING.

I keep getting errors, saying I'm not formulating my if statements correctly.

This is what I have:

Private Sub Workbook_Open()
If Range("a1") = "" Then macrox
else do nothing
End If
End Sub

I use a excel file through the course of the day and need to insert the current date in one column and the current time in the next column. I want to be able to just highlight the selected range of cells I need to insert into and hit a macro button and have the date and time inserted into just the cells I have highlighted. I'm not sure how to make this work with just the cells I've highlighted. Any help any one can give me would be greatly appreciated. Thanks!

Happy Day to all,

Can you please help me,

A1= time in
B1= time out
C1= time in
D1= time out

I want to calculate the late and under time,
Office start at 9:am w/30 mins Grace period,
The break time is one hour only, please include over breaktime in calculation.
End of office hours 6:00 pm, strickly no over time

I want to Sum Column "H" starting form "H2" all the way down (rows may vary)
Then Paste My answer in "AM1"
This is what I have so far and for some reason is not working.

myRange = ActiveSheet.Range("H2", Range("H2").End(xlDown))
Range("AM1") = WorksheetFunction.Sum(myRange)

Any Ideas?

I have a sheet which has the start, stop and time taken for lunch breaks.

I want to calculate the total time worked such that i have the hours worked. So if i worked 7 hours and 30min then the total time would be 7.5hrs. But i am not sure how to take into account the lunch break time (see enclosed sheet).

anyone have any ideas?

Hi, I'm trying to get some macros to combine data from two open workbooks, and I can't figure out the commands to switch from the active workbook to the other open workbook and back. I'm new to VBA and just learning the ropes. All I can figure out is how to switch to another workbook with an exact name. If I record the macro to go to a recent file, for example Window > 1 (filename), the code that I get in VBA is Windows("filename").Activate. This doesn't translate to when I have two different workbooks with different filenames open.

Make sense? I'm a bit confused myself. Maybe just a list of basic workbook-switching techniques or commands would be useful.

- Michael

Hey guys,

I'm trying to write a macro which prints to PDF and saves the file name as the contents of a cell. I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:\Documents and Settings\samb\My Documents\" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter. Any ideas where I'm going wrong? Any help would be much appreciated!

Related pages

how to use the offset function in excelheadcount calculation templateacroexch.appexcel spreadsheet for timesheetexcel csv unicodeerror handler in vbadaily inventory worksheetopening a dbf filecannot find installable isamapplication worksheetfunction findvba maximum valuegant calendarcombine hlookup and vlookupconditional formatting wordshow to turn off scroll lock in excelpc miler online freepayback graphlocking row in excelhourly rate converterreverse paypal calculatorhyperion essbase excel add-inweekday in excelsequential number generator excelconcatenate excel 2003create timesheet in excellinear interpolation excelexcel cagrlikert scale generatoropening gz filesexcel macro save as xlsxcount if rangemerge two cells into one in excelbubble chart excel 2013negative gross profit marginsales tracker excel templateexcel outliershow to insert comment in excelvlookup and if statementdelete duplicate rows excelconvert decimal degrees to meters formulacelllinkexcel calendar dropdownwaterfall charts with negative valuesvlookup with concatenatecombine multiple sheets into one sheet excelhow to make a tick mark in excelexcel formula between two numbersattendance point system spreadsheetwk4 extensionexcel vba close filevba scripting dictionaryapplication displayalertshow to continue numbering in excelcompound interest worksheet excellotus notes export to excelhow to record keyboard strokeshow to calculate upper control limit in excelupdate outlook calendar from excelleaguesecrataryexcel vba checkbox checkedexcel pivot table calculated fieldshow to sort dates in excelslope calculation in excelfindwindowexgrid ref converterpercent increase formula excelexcel formula for variancecrc16 calculatorexcel max valuethe middle characterexcel row limit