I have a form that finds an loads a record in textfields and combo boxes fine. When there are more than 1 record found, the user is presented with the FindAll option. Upon clicking find all, the list box gest populated with the records found. But instead, I am getting Run-Time error:'9' Subscript out of range in the following code block. It is occurring in the block starting with myArray: - MyArray(i, 0) = fndA:
Private Sub cmbFindAll_Click()
Dim MyArray(6, 12)
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL As String
Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12 As String ' heading s for list
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("A6", Range("A65536").End(xlUp))
strFind = Me.txtWorkOrderNo.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
'load the headings
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).
In Excel I've declared an array outside of any sub or function.
Dim programList() As String
Now I want to use that array, populating and depopulating as needed by calling these subs from elsewhere:
Sub addProgramToArray(ByVal x As String) ' Adds worksheet names to an array for use with adjusting program lengths If IsArray(programList) Then thisCount = UBound(programList) End If Redim Preserve programList(thisCount + 1) programList(thisCount) = x
End Sub
Sub removeProgramFromArray(ByVal x As String) ' Removes worksheet names from array, for use with adjusting program lengths ' We do this without resorting the array If IsArray(programList) Then For i = 0 To UBound(programList) - 1 If programList(i) = x Then programList(i) = "" End If Next End If
End Sub
But when run it generates an Error 9 Subscript Out of Range error on the addProgram or removeProgram subs. I suspect it might be the ubound on an empty array, or the fact that I'm trying to use an array I declared outside of the subs.
In my VBA project I've declared several public variables as normal (ahead of all procedures) ... Public simolo() As double etc. They are declared in normal modules, and arent declared twice. I set the values in one procedure and then execute a second procedure but, when the variable is encountered in the second procedure, it appears to be empty and I get a "Subscript out of range" error. Clearly, the public variable isn't public since no data is stored in it. What is going on?
I found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:
I am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are: completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls cashsheetname Formatted Sheet cashcurrcolumn A cashfirstrow 2 cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _ Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _ Range((PELCurrColumn & PELlastrow)).Value
I have a master spreadsheet in XL2003 (SP3) running Vista. The master sheet, called "DCam IV-11.xls" is a large calculation sheet that is driven by user entered boundary conditions. The boundary conditions are stored in different input files named "******-inp.xls". I use this method to store BCs in small spreadsheet files rather than saving full master spreadsheets in files.
keep getting an error message saying subscript out of range.
What am i missing?
'setting up the variable in the projectManagersWorkbook object Set projectManagersWorkbook.Wkbk = Workbooks(getfileName(projectManagersWorkbook.filePath)) Set projectManagersWorkbook = projectManagersWorkbook.Wkbk.Worksheets("project managers") Set projectManagersWorkbookLookupCell = projectManagersWorkbook.wks.Range("A2") ' searching for the Last Status Column from the last PO spreadsheet vlookupcolumn = 1 Do Until tempValue = "Project Manager" tempValue = projectManagersWorkbookLookupCell.Offset(-1, vlookupcolumn) vlookupcolumn = vlookupcolumn + 1 Loop
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.
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] .....
I have this code below that keeps giving me a subscript out of range error and I don't know why I got it to run once but can't figure out the problem now...
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.
I'm getting a "Subscript out of Range" error on a routine that was running fine a few days ago. It's part of a larger macro, so I've pasted the full code for that macro below. The problem is in the routine labled "Replace Regional Labels". What that's supposed to do (and DOES do) is to replace labels such as 110 or 11000-CSD with 11000. The error message comes up AFTER the routine has completed it's work. The offending line is:
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?
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)
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
When it reaches Set wbdata it says subscript out of range. I'm not sure how it cannot recognise the spreadsheet I just created and that is open and working.
I am getting he Subscript out of range error when trying to run this macro. I have the macro on a macro workbook and I am referencing another spreadsheet.
Sub Dater() Static Count As Integer Dim Wb As Workbook Dim Ws As Worksheet Dim FileStr As String Set Wb = Workbooks("Pending Approval by Responsible User") Set Ws = Worksheets("Pending Approval by Responsible")
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.
I have recorded a macro that will copy and paste information from text files to two separate sheets in a workbook. Previous versions of this have worked fine but now I have a "subscript out of range problem" for this line: Sheets("Raakadata").Select
I'm doing this project to someone else and the weird thing is I don't see this error in my computer but they have it in theirs. The workbook has been recently renamed to oljy_vedesta.xls but it should be correct in the code. The syntax should be fine as macro recorder has been used but still this error comes up with the another computer (all the required files are there).
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.
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.
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.
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
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.
I have a workbook with 20 sheets, including a control sheet 'Validation Table'. By setting the required Background/Font in a cell named hdrDefault on sheet VT, I want to click a button on VT to invoke a macro to visit each sheet and set the named Header range, hdrxxx, to the same Background/Font. Originally had the code in a Private Sub linked to a button event, but this can't break out of the worksheet so have moved the Sub into a Module, and calling from the click event:
