I am trying to declare a dynamic sized array. When the code runs, I get 2 errors:
Compile error, array already dimensioned (on the redim line)
when I remove the redim statement I get
error 9 - subscript out of range
I need to create this code to work in excel as well as an application running VBScript.
Dim myarray()
myarray(1) = "a"
myarray(2) = "b"
'etc...
Redim preserve myarray(UBound(myarray))
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
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).
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.
Code:
Private Sub Hundred_Day_MA()
'Sets Last Trading Day's Date
Dim LastDate As Date: LastDate = ThisWorkbook.PreviousBusinessDay(Now, 1)
[Code]....
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 have a quick question in relation to filling a range with an array.
When I use the following code:
Code:
Sub justdoit()
s = Array(1, 2, 3)
Range("A1:C1") = s
End Sub
It works fine. but when I try to populate the range in rows instead of colums, the array value stays as the first value.
Code:
Sub justdoit()
s = Array(1, 2, 3)
Range("A1:A3") = s
End Sub
I'm experimenting with arrays and would like to get better at them, but this has me stumped for the moment. How to get the values to work going down, like they do going across.
The above code is a small sample of data, I am wating to replicate with using over 80K lines and cell by cell is too slow.
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:
View 3 Replies View Relatedfilling an Array then I have used in this macro?
View 3 Replies View RelatedIm doing an assignment for my VBA class and im in a jam.I have a form with a listbox and numerous textboxes. The user is to select a name and then the information from this name fills the corresponding boxes.
First off, my teacher is horrible, i have to learn by just reading so that's why I'm so puzzled. I have an array i made by importing/parsing a text file. I've attached the .txt file for reference. I currently want to add just the names from the array to then when the user selects the name, fill the remaining boxes with the corresponding information.
This is what I have thus far...
Code:
Dim nValues As Integer
textFile = "C:UsersMattDocumentsemployeedata.txt"
Open textFile For Input As #1
[Code]....
need it to ONLY display the name, but then display ALL names in the array. What do i need to do?
Filling all elements of a two dimensional array?. I know that I could write something like:
View 3 Replies View RelatedI am trying to use the below code to fill a combobox with unique values. It works great except I can't figure out a way to replace BLOCK 1 with BLOCK 2. I want to use BLOCK 2 to populate mgNames so that only specific values are filled into mgNames. Currently BLOCK 1 just fills mgNames with an entire range without any conditions. how I can get BLOCK 2 to work in the way i'm intending it to?
Code:
Sub findNames()
Dim mgNames As Variant
Dim myCollection As New Collection
[Code]....
I am trying to make an "Occurrence Selector" for our documents, because we want to import some data from HTML files we recieve and fill in a CSV Template. The problem is, the HTML files we receive contains a lot of information, and about half of the information is usefull for us. Therefor we want to select some occurrences and not all of them.
All i want is the user to input the occurrence number in the inputbox, and then the macro simply search for "Occurrence #(firstinput)",does its job, then "Occurrence #(secondinput)",does its job again, and so on until all of the required occurences have been transplanted into the CSV file.
This is what i currently have
VB:
Dim jobtodo As Variant
Set jobtodo = Application.InputBox(Prompt:="Type in each occurences that need to be inserted in the upcomming CSV file. Please seperate them all with a COMMA (,) between them.", Title:="Occurence Selector", Default:="1,2,3,4,...", Left:=150, Top:=150, Type:=64)
MsgBox (jobtodo)
Type is 64 because i want an Array, (i guess?)
When i input anything, i get an error message stating my formula has an error.
I am trying to fill an array with only a few values within a loop (when XspacingA is 0.5, add the value of the Line to the array) and keep getting the error "Subscript out of range".
View 3 Replies View RelatedI 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 had the worksheet (VCollinsville) misspelled in the Workbook.
I have the following code (it's long but my problem is at the end where I stop): ...
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.
View 6 Replies View Relatedkeep 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
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.
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] .....
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
Worksheets("History").Activate
Range("G2").Select[code]....
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.
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...
View 4 Replies View Relatedtrying to copy some records across from once sheet to another?
butt it keeps debugging?
any idea why.
i'd like to keep the user defined type because it's neat and is good practice for myself?
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 RelatedI'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:
View 2 Replies View RelatedI 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)
This part of he code gives error
Code:
arrOP_PS(PSCounter, 1) = CDate(Data(CurrentRow, 1)) 'date
Code:
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] ........
I created a code and I just changed computer. The code that was working perfectly fine on my previous laptop is no longer working now.
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:UsersUserDocumentsBCB MANAGEMENTBankArchiveData_TenantsData_Tenants_" & Left(strDate, 8) _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Set wbData = Workbooks("Data_Tenants_" & Left(strDate, 8))
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")
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.