This is a very slow method.
You are actually looking at each cell in column A. Ok if there only a few rows
But when there are thousands this is very slow.
Try using the Find function.
There are two approaches that I would use Depending on your data.
Solution A:
Search for your search data in column A and check if B and C also match.
Solution B:
use Countif on columns A, B and C to find how many times your search data appears in those columns.
If we search for the least common result then we could be minimising our work.
Sub Matress58()
MyArray = Array("Dog", 42248, "San Jose")
Myarray2 = Array(1, 1, 3)
LR = Selection.SpecialCells(xlCellTypeLastCell).Row
For Count = 0 To 2
Myarray2(Count) = Application.CountIf(Range(Cells(1, Count + 1), Cells(LR, Count + 1)), MyArray(Count))
Next
Set rngLook = Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row)
SearchCol = Application.Match(Application.Min(Myarray2), Myarray2, 0)
SearchText = MyArray(SearchCol - 1)
With Range(Cells(1, SearchCol), Cells(LR, SearchCol))
Set rngFind = .Find(SearchText, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With
If strFirstAddress = "" Then Exit Sub
rngPicked.Select
For Each c In Selection
If Cells(c.Row, 1).Value = MyArray(0) And Cells(c.Row, 2).Value = MyArray(1) And Cells(c.Row, 3).Value = MyArray(2) Then Cells(c.Row, 4).Select: Exit For
Next
Bookmarks