Excel Vba Subscript Out Of Range Error 9 Array
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring excel vba runtime error 9 subscript out of range developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question
Vba Subscript Out Of Range Error 9 Worksheet
x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them;
Subscript Out Of Range Vba
it only takes a minute: Sign up Excel VBA - Run-time error '9', Subscript out of range up vote 2 down vote favorite 1 I really appreciate any help I can get on this. I'm trying to loop through
Run Time Error 9 Subscript Out Of Range Excel
a column looking for duplicate names then taking that and several of other data from same row and placing them into an 2D array that I want to use another function, but it's not working. I really need your help figuring out why I cannot redim this array without preserving the data. Dim oRange As Range, aCell As Range, bCell As Range Dim ws As Worksheet Dim SearchString As String, FoundAt As String Dim tArray() As Variant Dim iR As run time error 9 subscript out of range excel 2010 Long Dim LastRow As Long Dim LastCol As Long 'name of the worksheet Set ws = Worksheets("VML Daily") 'column 6 has a huge list of names Set oRange = ws.Columns(6) 'the keyword (there are 7 'ABC Company 1' in the column above) SearchString = "ABC Company 1" 'Find keyword in column Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) 'find last row and column number LastRow = Range("A1").End(xlDown).Row 'redimensioning based on maximum rows ReDim Preserve tArray(1 To LastRow, 1 To 3) As Variant 'if search finds something If Not aCell Is Nothing Then Set bCell = aCell FoundAt = aCell.Address iR = 1 tArray(1, 1) = aCell tArray(1, 2) = aCell.Offset(0, 33) tArray(1, 3) = aCell.Offset(0, 38) 'continue finding stuff until end Do Set aCell = oRange.FindNext(After:=aCell) If Not aCell Is Nothing Then If aCell.Address = bCell.Address Then Exit Do FoundAt = FoundAt & ", " & aCell.Address tArray(iR, 1) = aCell tArray(iR, 2) = aCell.Offset(0, 33) tArray(iR, 3) = aCell.Offset(0, 38) iR = iR + 1 Else Exit Do End If Loop 'redim'ing the array to the amount of hits I found above and preserve the data 'Here's where it error's out as "Subscript out of range" ReDim Preserve tArray(1 To iR, 1 To 3) As Variant Else MsgBox SearchString & " not Found" Exit Sub End If excel vba excel-vba share|improve this question edited Dec 18 '13 at 20:42
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack run time error 9 vba Overflow the company Business Learn more about hiring developers or posting ads with us subscript out of range excel macro Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community runtime error 9 subscript out of range fix of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Subscript Out Of Range (Error 9) on ReDim in VBA up vote 1 down vote favorite http://stackoverflow.com/questions/20667785/excel-vba-run-time-error-9-subscript-out-of-range I am getting an error when trying to create a new Member in my "Shifts" class. This sub is meant to fill the following variables (which are declared at the top of the class): Private ShiftMembers() As String Private ShiftCallSigns() As String Private ShiftAssignments() As String Private ShiftStatuses() As String Public Sub AddMember(ByVal Name As String, ByVal CallSign As String, ByVal Assignment As String, Optional ByVal Status As String) If IsEmpty(ShiftMembers) http://stackoverflow.com/questions/28228746/subscript-out-of-range-error-9-on-redim-in-vba = False Then ReDim Preserve ShiftMembers(UBound(ShiftMembers) + 1) ReDim Preserve ShiftCallSigns(UBound(ShiftCallSigns) + 1) ReDim Preserve ShiftAssignments(UBound(ShiftAssignments) + 1) ReDim Preserve ShiftStatuses(UBound(ShiftStatuses) + 1) Else ReDim Preserve ShiftMembers(0) ReDim Preserve ShiftCallSigns(0) ReDim Preserve ShiftAssignments(0) ReDim Preserve ShiftStatuses(0) End If ShiftMembers(UBound(ShiftMembers)) = Name ShiftCallSigns(UBound(ShiftCallSigns)) = CallSign ShiftAssignments(UBound(ShiftAssignments)) = Assignment ShiftStatuses(UBound(ShiftStatuses)) = Status End Sub When I call this Sub, I get a "Subscript Out Of Range (Error 9)" message. Any ideas? Thanks! I created the "IsEmpty()" check because I believe UBound will throw an error if the array has 0 elements (right?). Thanks in advance! -Rob excel vba excel-vba share|improve this question asked Jan 30 '15 at 3:27 Robert Gagnier 154 even if no data is inside the variables (in this case arrays), if the variable is created, isempty will answer false –Patrick Lepelletier Jan 31 '15 at 9:52 add a comment| 2 Answers 2 active oldest votes up vote 0 down vote accepted The problem is these lines: ReDim Preserve ShiftMembers(UBound(ShiftMembers) + 1) ReDim Preserve ShiftCallSigns(UBound(ShiftCallSigns) + 1) ReDim Preserve ShiftAssignments(UBound(ShiftAssignments) + 1) ReDim Preserve ShiftStatuses(UBound(ShiftStatuses) + 1) Are accessed when no bounds are defined, so UBound throws the error. IsEmtpy will not check array bounds and if you explicitly declare the bounds at 0 then you will not be
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business http://stackoverflow.com/questions/31932346/run-time-error-9-subscript-out-of-range-with-dynamic-array Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs http://stackoverflow.com/questions/26424403/vba-excel-fixing-runtime-error-9-subscript-out-of-range Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Run-time error '9': Subscript out of range with Dynamic Array up vote 0 down vote favorite Im trying to add a value to subscript out a dynamic array but keep getting a run-time error with this. I've found a few different sources saying that this should be the answer, so I cant figure out what I have done wrong... Sub addtoarray() Dim catSheets() As String ReDim Preserve catSheets(UBound(catSheets) + 1) catSheets(UBound(catSheets)) = "Chemicals" End Sub vba excel-vba share|improve this question asked Aug 11 '15 at 2:41 Sanfly 137119 add a comment| 3 Answers 3 active oldest votes up vote 3 subscript out of down vote When you create the catSheets() array, it's dimensionless. Therefore, you cannot use UBound() to determine the array's upper boundary. You can certainly follow up a Dim () with a ReDim if you want to specify an array size, but you won't be able to query the array dimensions until after you've given it some. So you have a few options. First, you could follow up your Dim () with an immediate ReDim to give your array an initial size: Dim catSheets() As String ReDim catSheets(0) As String ... ReDim Preserve catSheets(...) As String Or, you could just use ReDim from the start to assign an initial size and still have the ability to ReDim later: ReDim catSheets(0) As String ... ReDim Preserve catSheets(...) As String Alternatively, you could use the Array() function and store it as a Variant. Done this way, you can query the UBound(). For example: Dim catSheets As Variant catSheets = Array() Debug.Print UBound(catSheets) ' => -1 ReDim Preserve catSheets(UBound(catSheets) + 1) catSheets(UBound(catSheets)) = "Chemicals" ' Works fine share|improve this answer edited Aug 11 '15 at 3:02 answered Aug 11 '15 at 2:53 Bond 12.1k61441 add a comment| up vote 0 down vote @Bond covered the main issue, and to expand Your initial sub was missing one statement: ReDim catSheets(0) to initialize it Option Explicit Sub a
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA EXCEL: Fixing “Runtime-error '9'”: Subscript out of range up vote -1 down vote favorite I have the below code which attempts to open a file name which changes on a daily basis if the sheet is empty. The file name is always named as such: "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt" The reason behind this is to automate the file activation instead of copying, pasting, and renaming the file every time. I have received this error when I updated the code to include the above mentioned path. Can someone point to me out where my error is? You may find the code below: Private Sub UserForm_Initialize() Application.ScreenUpdating = False If IsEmpty(A_Regular.Range("A2")) Then Dim TxtPath, TxtName As String TxtPath = "K:\Shared\Num\Temp\Available_list_" & Year(Now()) & Month(Now()) & Day(Now()) & ".txt" TxtName = Year(Now()) & Month(Now()) & Day(Now()) & ".txt" Workbooks.OpenText Filename:=TxtPath, Origin:=437, StartRow:=1, _ DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Tab:=True, Semicolon:=False, Comma:=False, Space:=False, _ Other:=True, OtherChar:=",", FieldInfo:=Array(Array(1, 1), _ Array(2, 1), Array(3, 5), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True '-------------------------------------------------------------------------' ' Setup Sheet ' '-------------------------------------------------------------------------' With Workbooks(TxtName) '-------------------------------------------------------------------------' Dim TotalFree, TotalFields As Double TotalFree = Application.WorksheetFunction.CountIf(Range("D:D"), "FREE") Range("A1:E" & TotalFree).Copy End With Workbooks("Matcher.xlsm").Activate Activation.Visible = True Activation.Activate With Activation Range("A:E").PasteSpecial Range("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove Range("A1") = "GSM" Range("B1") = "Type" Range("C1") = "Date" Range("D1") = "Status" Range("E1") = "Number" Range("F1") = "Pattern" Call Encryption End With Activation.Cells.Clear Windows(TxtName).Close Control.Activate Activation.Visible = False