swaggerbox
09-03-2015, 01:50 AM
I am new to VBA and have problems with putting values into an array. The macro below works fine when I manually input the array values, e.g. Array("criteria1", "criteria2", "criteria3", "criteria4", "criteria5", "criteria6", "criteria7", "criteria8"). However, when I use a range to define the array, e.g. arr = Sheets("Sheet1").Range("B5:B12"), it generates a run-time error 9 subscript out of range at this line "If InStr(1, strTmp, arr(i), vbTextCompare) > 0 Then". What did I do wrong and how do I fix this?
Sub SearchMatchInTextFile()
Const ForReading = 1, ForWriting = 2
Dim FSO, FileIn, FileOut, strTmp
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FileIn = FSO.OpenTextFile("D:\filelist.txt", ForReading)
Set FileOut = FSO.OpenTextFile("D:\filelist2.txt", ForWriting, True)
arr = Sheets("Sheet1").Range("B5:B12")
'arr = Array("criteria1", "criteria2", "criteria3", "criteria4", "criteria5", "criteria6", "criteria7", "criteria8")
Do Until FileIn.AtEndOfStream
strTmp = FileIn.ReadLine
If Len(strTmp) > 0 Then
For i = 0 To UBound(arr)
If InStr(1, strTmp, arr(i), vbTextCompare) > 0 Then
FileOut.WriteLine strTmp
Exit For
End If
Next
End If
Loop
FileIn.Close
FileOut.Close
End Sub
Sub SearchMatchInTextFile()
Const ForReading = 1, ForWriting = 2
Dim FSO, FileIn, FileOut, strTmp
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FileIn = FSO.OpenTextFile("D:\filelist.txt", ForReading)
Set FileOut = FSO.OpenTextFile("D:\filelist2.txt", ForWriting, True)
arr = Sheets("Sheet1").Range("B5:B12")
'arr = Array("criteria1", "criteria2", "criteria3", "criteria4", "criteria5", "criteria6", "criteria7", "criteria8")
Do Until FileIn.AtEndOfStream
strTmp = FileIn.ReadLine
If Len(strTmp) > 0 Then
For i = 0 To UBound(arr)
If InStr(1, strTmp, arr(i), vbTextCompare) > 0 Then
FileOut.WriteLine strTmp
Exit For
End If
Next
End If
Loop
FileIn.Close
FileOut.Close
End Sub