Trying to search an Array of Strings
 

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

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