Trying to search an Array of Strings

[Aaron]

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

 

[Rick Rothstein \(MVP - VB\)]

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