Trying to search an Array of Strings
InStr can only search an individual text string, not a whole array of
individual text strings. If your array was one dimensional, you could get
some help from VBA's Filter function, but since DataArray is a
two-dimensional array, you will have to search it element by element by
iterating the rows and columns much as you did when you put the elements
into the array.
Rick
"Aaron" wrote in message
...
On the line followed by ?????? I get a type mismatch on the variable
DataArray. Am I using instr wrong?
Sub arraymatch()
Dim DataArray() As String
Dim OutputArray() As String
Dim TargetRange As Range
Dim OutputRange As Range
Application.ScreenUpdating = False
startTime = Timer
With Sheets("SCC AH")
r = 2
c = 3
DataInput = .Cells(r, c)
CellsDown = .Range("C2").End(xlDown).Row
CellsAcross = 2
ReDim DataArray(1 To CellsDown, 1 To CellsAcross)
For ArrayC = 1 To CellsAcross
For ArrayR = 1 To CellsDown
DataArray(ArrayR, ArrayC) = DataInput
r = r + 1
DataInput = .Cells(r, c)
Next ArrayR
r = 2
c = c + 1
DataInput = .Cells(r, c)
Next ArrayC
r = 2
End With
With Sheets("Report")
r = 2
SearchFor = .Cells(r, 1)
CellsDown = .Range("A2").End(xlDown).Row
ReDim OutputArray(1 To CellsDown)
For ArrayR = 1 To CellsDown
OutputArray(ArrayR) = InStr(1, DataArray,
SearchFor)?????????????????????
r = r + 1
SearchFor = .Cells(r, 1)
Next ArrayR
OutputRange = .Range(Cells(2, 3), Cells(CellsDown, 3))
OutputRange.Value = OutputArray
End With
Application.ScreenUpdating = True
MsgBox Format(Timer - startTime, "00.00")
End Sub
|