Help with Arrays

[swaggerbox]

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

[Aussiebear]

Do the terms Criteria # respond to the headings?

[snb]

Try to get familiar with 1-dimensional and multidimensional arrays:

http://www.snb-vba.eu/VBA_Arrays_en.html

[swaggerbox]

thanks for the link snb!

[Kenneth Hobs]

Sub ken()
Dim arr1 As Variant, arr2 As Variant

arr1 = WorksheetFunction.Transpose(Sheets("Sheet1").Range("B5:B12").Value)
arr2 = Array("criteria1", "criteria2", "criteria3", "criteria4", _
"criteria5", "criteria6", "criteria7", "criteria8")

MsgBox Join(arr1, vbLf), vbInformation, "arr1"
MsgBox Join(arr2, vbLf), vbInformation, "arr2"
End Sub

[swaggerbox]

thanks Kenneth!

[snb]

To add:



Sub M_snb()
MsgBox Join([transpose(A1:A8)], vbLf)
MsgBox Join([transpose(transpose(C1:F1))], vbLf)

MsgBox Join(Application.Index([C1:F2].Value, 1), vbLf)
sn = Range("C1:F2").Value
MsgBox Join(Application.Index(sn, 1), vbLf)
End Sub