I Keep getting thrown a Error 9 when Working with a Variant Array
This code is checking a file name against a array to determine if we need to operate on that file.
Code breaks when i attempt to explicitly assign array size. If i do not do that, code breaks during the string check saying If arr(i) is out of range of the array.
Not sure why, UBound and LBound works perfect, and the array is being assigned correctly from the local properties list.
'Loop through all files in Folder
Do While TargetString <> ""
Set SourceWB = Workbooks.Open(NavigationString & TargetString)
'This checks how full our list is and appends the array correctly
BankWS.Activate
'There is more than 2 items, append full list to array
If Not BankWS.Range("I3") = "" Then
BottomOfList = BankWS.Range("I2").End(xlDown).Address
OldReportsArray = BankWS.Range("I2:" & BottomOfList).Value
Else
'There is a single item or empty, append 1st value and empty 2nd
OldReportsArray = BankWS.Range("I2:I3").Value
End If
ReDim Preserve OldReportsArray(UBound(OldReportsArray) + 1)
'Check Array and SourceWB to see if we have already loaded in this report
If IsInArray(SourceWB.Name, OldReportsArray) = True Then
'Match found, Skip to next loop iteration
SourceWB.Close
GoTo NextLoop
End If`
'Check Passed String inside of Array, return true or false
Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
Dim i
For i = LBound(arr) To UBound(arr)
If arr(i) = stringToBeFound Then
IsInArray = True
Exit Function
End If
Next i
IsInArray = False
End Function