Trying to search an Array of Strings

A

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top