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
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