Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search for multiple strings | Excel Discussion (Misc queries) | |||
Search two Strings | Excel Programming | |||
Search for strings | New Users to Excel | |||
VBA search and compare strings | Excel Programming | |||
Search through individual data strings | Excel Programming |