Hello everyone
I have some items in column A in sheet1 ..and I intend to search for each item in the other sheets
The search will be through the used range of the sheet and return the value in the first column of the searched sheets
I have the following code that works well for small amounts of data but in real file it is very very slow so I am searching for a faster way
Sub Search_Column_Items_Across_Multiple_Sheets_Using_Arrays()
Dim wsh As Worksheet
Dim ws As Worksheet
Dim arr As Variant
Dim a As Variant
Dim v As Variant
Dim rng As Range
Dim i As Long
Dim x As Long
Set wsh = Worksheets("Sheet1")
Set rng = wsh.Range(wsh.Range("A2"), wsh.Range("A" & wsh.Rows.Count).End(xlUp))
arr = rng.Value
For i = 1 To UBound(arr, 1)
x = -1: Set ws = Nothing
For Each ws In ThisWorkbook.Worksheets(Array("1", "2"))
a = ws.Range("A1", ws.Cells(1, 1).SpecialCells(xlLastCell)).Value
x = SearchIn2DArray(a, CStr(arr(i, 1)))
If x <> -1 Then Exit For
Next ws
If x = -1 Then
arr(i, 1) = Empty
Else
arr(i, 1) = a(x, 1)
End If
Erase a
Next i
Application.ScreenUpdating = False
rng.Offset(0, 1).Value = arr
Application.ScreenUpdating = True
End Sub
Function SearchIn2DArray(vArr As Variant, strCrit As String)
Dim i As Long
Dim j As Long
SearchIn2DArray = -1
For i = LBound(vArr, 1) To UBound(vArr, 1)
For j = LBound(vArr, 2) To UBound(vArr, 2)
If vArr(i, j) = strCrit Then
SearchIn2DArray = i: GoTo Skipper
End If
Next j
Next i
Skipper:
End Function
* The issue is posted here too
http://www.eileenslounge.com/viewtopic.php?f=30&t=29498
Bookmarks