Subscript Out Of Range Error When Using Nested If Statement

Aug 27, 2008

I have a Userform which has 2 textboxes. User enter his employee id on textbox1 then enters a valid break time code which are 1,2 and 3 on textbox2. If User typed 1 then first breaktime start time will be pasted on the worksheet. When User returns after break he then enters his employee id again and then type 1 to end his first breaktime, the end time will be pasted on the worksheet. My program works fine however, I am trying to place a code which will prompt the user to end his first break time before entering another valid break code. Kindly check my code below as I am receiving an error message. 'Run-time error 9' Subscript out of range. This thing is driving me nuts. Any advise or can you tweak my coding vba experts.

View 14 Replies


On Error Statement: Subscript Out Of Range. Go To Sheets

Sep 27, 2006

Sub SheetFinder()

Application.MacroOptions Macro:="SheetFinder", _
HasShortCutKey:=True, ShortcutKey:="G"

msg1 = "What sheet would you like to go to?"
msg2 = "I'm sorry, but that sheet could not be found."
On Error Goto NotFound

prompt1 = InputBox(msg1, , "Template")

If prompt1 = "" Then
End If


View 9 Replies View Related

Subscript Out Of Range Error

Jun 26, 2014

I keep getting an error message when running the following code.

I am not sure why, but I keep getting a "Run-Time error '9': Subscript out of range" error message.

View 3 Replies View Related

Getting Subscript Out Of Range Error

Jul 23, 2014

What I'm trying to do is change the text color of specific keywords in a spreadsheet. For example, I'd like to highlight the text of 'how to' in the following sentence "I want to know how to change text color", so that it looks like this "I want to know how to change text color".

I came across the following, and it works for only two terms (example, "how to" , "how do I"), but I have a string of about 15 terms I'd like to use, but anything I change over two, I get the subscript out of range error.

[Code] .....

View 13 Replies View Related

Subscript Out Of Range Error 9?

Feb 25, 2014

what I'm trying to do is automatically format Excel's track changes to look like Microsoft Word's track changes feature i.e. put in a strike-through when people make changes on a shared spreadsheet.

I found the following script from here: [URL] .....

Dim xLen As Integer
Dim x, y, z, addr As String


Unfortunately, that produces the "Subscript out of range (Error 9)."

Clicking debug will highlight 'Worksheets("History").Activate', but I'm assuming that will just be one problem among a few others.

I've also tried replacing the 'x' in 'Worksheets(x).Activate' with the name of the worksheet (with extension xlsm), but no joy.

View 1 Replies View Related

Subscript Out Of Range Error ...

Jul 14, 2009

I am having trouble in a dowhile loop that i have created. Its purpose is to copy an unknown amount of data points into an array while it counts how many data points it collects and how many of them are above a reference pressure. I have defined data_A(),data_points,above_reference, and reference_pressure all as integers. The error occurs in the second line of code.

View 14 Replies View Related

Subscript Out Of Range Error

Sep 21, 2011

I have a workbook called "Project" and in it there is a macro that pulls data out of another workbook called "Operations Report."

The macro works fine on my laptop. When I run the macro, I make sure that both workbooks are open, (and they are also saved in the same directory - not sure that matters).

I sent the two workbooks to a colleague and when she opens them both and runs the macro, she gets a "run error 9 subscript out of range" error. She too has saved them to the same directory.

why she might be getting the error when I do not? Here is the line where hers trips up:

With Workbooks("Operations Report").Worksheets("Sheet2")

I assume that the macro is not finding the "Operations Report" workbook.

Are there some checks we could do or test lines of code I could send to her to diagnose the problem?

View 9 Replies View Related

Error 9 Subscript Out Of Range

Dec 23, 2013

Error 9 - Subscript Out Of Range....All sheets are in workbook and spelt correctly....could it be in the Array or Loop part of the code.

It also in the userform have a combobox for Day,Afternoon and Night but it transfers all across.Need the code to just transfer what is selected from the combobox (could be a listbox as well).If day selected then only Day for the selected date,,,same for afternoon and night unless All selected in which all are transferred (DAy,Afternnon & Night)

This part of he code gives error

arrOP_PS(PSCounter, 1) = CDate(Data(CurrentRow, 1)) 'date


Dim Data
Dim arrOP_PS(1 To 6, 1 To 5), PSCounter As Long
Dim arrOP_MSI(1 To 6, 1 To 5), MSICounter As Long
Dim arrOP_MSE(1 To 6, 1 To 5), MSECounter As Long

Private Sub CommandButton1_Click()

[Code] ........

View 6 Replies View Related

VBA Error, Subscript Out Of Range, 9

Nov 16, 2007

Sub Split_By_Store()

x = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox x

a = "F1:F" & x

Range(a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1"), Unique:=True

y = Cells(Rows.Count, 10).End(xlUp).Row

For b = 2 To y
Worksheets.Add.Name = Worksheets("data").Cells(b, 10)
Next b

For c = 2 To x ' copy the data now
d = Worksheets("data").Cells(c, 6)
z = Worksheets(d).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("data").Range("A" & c & ":I" & c).Copy
Worksheets(d).Cells(z + 1, 1).PasteSpecial
Next c

End Sub

This code breaks on the line z =, I get a Subscript out of range:9 error. Basically this starts with a single sheet called "data". Column F has store number and column J is created as a helper column and each unique store number is transfered there... Then sheets are created for each unique store number from the helper column. Then it's supposed to transfer the area A:I from the "data" sheet for each store that is unique from column F and transfer that to the corresponding Store number for the sheet that was created for it in the earlier step.

View 9 Replies View Related

Subscript Out Of Range Error

Jul 25, 2007

I have in the attached workbook, a form which I run, the macro StartForm. This brings up the form, where in A, I select the sheet label Oval_An, and in B, I select the sheet Oval_DMA. The other drop down boxes are not in use yet. Once A and B are selected, the user hits the big subtract button, and normally gives the the new sheet, Final Results.

Yet, when I change the name of the forms, or allow the kTest compare variance box into the macro to be user definable via the form, the whole thing just stops working.

The error is found in the kTest macro, line 12 as a type mis match.

Redim q(1 To UBound(a, 1), 1 To 1)

I have had a whole lot of help from people with this, and I am new to VBA.

View 9 Replies View Related

Run Time Error - Subscript Out Of Range 9

Jul 16, 2014

I have three sheets in My workbook and one user form. based on selection of checkbox in userform, I want to activate the respective sheets. While clicking on add button, I am getting error message " Run Time Error : Subscript out of range 9". I am using following code

Option ExplicitPrivate Sub Add_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
TransferValues ctrl

[Code] .....

I am getting error on "Set ws = Sheets(Left(cb.Name, 1))" line.

View 9 Replies View Related

Runtime Error 9 - Subscript Out Of Range

Feb 12, 2014

I am trying to consolidate data in Sheet7 of all the files in a folder to a single file. In case a file doesn't contain Sheet7, i want to get the name of the workbook. The code is working fine, but after it loops it is returning Runtime Error 9 - Subscript out of range.

[Code] .....

View 5 Replies View Related

Error Message 'Subscript Out Of Range'

Aug 24, 2009

See if you can see what is wrong with this code - I have no idea. All the spreadsheet names are correct and I have used this syntax before, I don't know what I am doing wrong. The macro has a problem with the 3rd line

View 2 Replies View Related

Run Time Error 9 : Subscript Out Of Range

Aug 27, 2009

This program is supposed to take the value in two combo boxes and use them to populate pivot charts that are in other spreadsheets. So a user would select PS and AMI on sheet one (s) and it would change the pivot table on s1-s9 to look up those chosen fields. I keep getting a subscript out of range error, and I'm not sure if my pivottable.pivotlayout method is correct, but when I recorded a macro (in the very bottom) using activesheet, it worked. How can I fix this?

The error occurs at the first large text, the second large text is the recorded macro.

View 14 Replies View Related

(2000) Subscript Out Of Range Error 9?

Oct 30, 2009

I cant figure out why this bit of code wont run without an error. Code in RED is the problem.

I am running xl2000 on NT4.Old

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

View 3 Replies View Related

VBA Runtime Error 9 Subscript Out Of Range

Feb 21, 2012

The internet normally has the answers but I have not found one for this runtime error. I am just trying to paste some values from Excel into a word table using the following code

'Paste the values into the word table
Set MyTable = Union(Range("a120:a124"), Range("a149:a152"), Range("a177:a181"), Range("a206:a213"), Range("a239:a240"), _


View 4 Replies View Related

Subscript Out Of Range Error In Array

Mar 21, 2012

I am pasting the VBA code below:

Public No_of_Sims As Variant
Public Sim_No As Variant
Public Ground_Up_Agg As Variant
Public UseSelfInsuredVehicle As String
Public Option_Name(1 To 12) As String

[Code] .....

The code highlighted in BOLD is where I am getting runtime error 9 (Subscript out of range).

View 7 Replies View Related

Subscript Out Of Range - Array Error

Dec 30, 2012

When I run my code it comes up with Error 9. I clicked debug and took a look at it and couldn't find out why it wasn't working.

Private Sub Hundred_Day_MA()
'Sets Last Trading Day's Date
Dim LastDate As Date: LastDate = ThisWorkbook.PreviousBusinessDay(Now, 1)


View 5 Replies View Related

Name Of Workbooks - Subscript Out Of Range Error

Apr 7, 2014

I get the subscript out of range error in the following code in the lines with set sk1 and set sk2. The problem disappears if I use the name of the workbooks (i.e. Workbooks("Sumy1") but I'd rather go with the variable which comes from the input box as the name may be different in the future.

Sub Sumy()

Dim Sumy1 As String
Dim Sumy2 As String
Dim sk1 As Workbook
Dim sk2 As Workbook

SumyKontrolne1 = InputBox("Podaj scieżkę pliku", "Ścieżka pliku", "C:PathSumy1.txt")

[Code] .......

View 4 Replies View Related

Run Time Error 9 - Subscript Out Of Range

Jan 20, 2009

I am trying to copy from a text file to excel workbook... Thw work book have 3 sheets: Sheet1, Sheet2, Sheet3
Now i want my macro to write into row D of Sheet2...

Sub copy_txt_files()
Dim FSO As Object, Folder As Object, file As Object
Dim copyFrom As Workbook
Dim wksCopyTo As Worksheet, wksCopyFrom As Worksheet
Dim rngCopyTo As Range, rngCopyFrom As Range
Set wksCopyTo = ThisWorkbook.Sheets(2)
Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:logs")
For Each file In Folder.Files
If LCase(Right(file.Name, 4)) = ".txt" Then
Set copyFrom = Workbooks.Open("C:logs" & file.Name)
Set wksCopyFrom = copyFrom.Sheets(1)
Set rngCopyFrom = wksCopyFrom.Range("D1")
Set rngCopyFrom = wksCopyFrom.Range(rngCopyFrom, _

View 9 Replies View Related

Runtime Error 9, Subscript Out Of Range

Apr 16, 2006

I used the macro recording thing to record a code to do OLS regression, added a few things and put it in a bigger program, but it's not working. It gives me a "Subscript out of range" message.

Here's the code I added: ...

View 9 Replies View Related

Run-time Error '9' Subscript Out Of Range

Jan 31, 2007

Sub button3_click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Workbooks("USA Appliance RepairUSAAR Phone Contacts DB.xls").Worksheets("Data")

' find first empty row in database
iRow = ws. Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 1).Value = This.ContactID.Value
ws.Cells(iRow, 2).Value = This.scheduledate.Value
ws.Cells(iRow, 3).Value = This.takenby.Value
ws.Cells(iRow, 4).Value = This.calldate.Value....................

Nothing complex, right? Well I am getting the error that is in the title of this message, and it highlights that third line, where I am setting the database. How can I fix this? Why does it say subscripts when there isn't anything with subscripts in it in the line?

View 8 Replies View Related

Runtime Error '9' Subscript Out Of Range

Mar 13, 2007

I see a few people have had this problem but I still cant work out what is going on in my code....

View 6 Replies View Related

Run Time Error '9': Subscript Out Of Range

Jun 20, 2007

I cant seem to fix this runtime error. I believe i have got it down to the exact line which i marked with (PROBLEM). Can you help me out?

Sub Search()

Dim Filename As String
Dim Search As String
Dim Result As String
Dim Resultant As String
Dim intCount As Integer
Dim strOut As String
Dim lngLoop As Long
Dim Link As String

View 3 Replies View Related

Subscript Out Of Range Error When Adding A Sheet

Dec 16, 2009

My macro adds a sheet to an existing Excel workbook. However, this only works the first time. If I delete the added sheet and run the macro again I get the above error. The macro below call a second macro (see later )

View 2 Replies View Related

Subscript Out Of Range Error When Adding New Sheet

Jun 14, 2013

I am trying to allow a user to select a file and have the only sheet from that file added to the workbook they are working in. When I run this code I get a "Subscript Out of Range" error

Sub importRawData()
Dim rawDataSheet As String
MsgBox "Please select the unmodified AR Aging Report exported from PFW", vbOKOnly
rawDataSheet = Application.GetOpenFilename(FileFilter:= _
"Microsoft Excel Workbooks, *.xls; *.xlsx", Title:="Select File")
Sheets.Add(Sheets("PWF AR Data"), , , rawDataSheet).Name = "PWF Raw Data"
End Sub

View 7 Replies View Related

Code Failing To Subscript Out Of Range Error

Jun 21, 2006

I have the following code which works successfully on my Excel 2002 PC and my Excel 2003 PC.

However when I take it to a clients machine it fails...

View 9 Replies View Related

Nested If Statement Within Range

Apr 7, 2009

I dont know how to nest and if (if that is the right way to go about it). this is what I have ....

View 10 Replies View Related

Activating Open Book - Subscript Out Of Range Error?

May 20, 2012

The code opens the book but the activation line gets a subscript out of range error.

Workbooks.Open File

View 1 Replies View Related

Subscript Out Of Range - Error Relating To Setting Workbook

Jul 11, 2014

I'm trying to do the following

Dim wkb As Workbook
Dim wkb2 As Workbook

Set wkb = ActiveWorkbook
Set wkb2 = Workbooks("F:SuppliersBT Monthly Invoice2014BT Macros.xlsm")

but get a subscript out of range error relating to setting wkb2

The file BT Macros is open - this is where the Macro is stored. What have I done wrong when trying to reference it?

View 5 Replies View Related

Copyrights 2005-15, All rights reserved