Most efficient way to search a range

[Ken Warthen]

I have a named range with eight columns and 3700 plus rows. I need a fast
and efficient manner of searching for all incidents of a given name in column
E and return the values of column B for each row where a column E value
matched the search name. I've kludged some code that works, but it's takes
way too long to return results. Any help or direction would be greatly
appreciated.

Ken Warthen

 

[Eric G]

I usually import all the data into memory to speed things up, rather than
referencing cells and ranges directly. When I'm done, I can quickly blast
the data back onto the worksheet, or onto a new sheet, etc. Here's some
sample code of what I would do.

HTH,

Eric

Option Explicit
Option Base 1
'
Sub Test_It()
Call Find_Name("Joe2") ' I made up data...
End Sub
'
Sub Find_Name(theName)
Dim i As Long, j As Long
Dim nRows As Long, nCols As Long
Dim theData() As Variant
Dim resultVector() As Variant
'
ActiveSheet.Cells(1, 1).Select
nRows = ActiveCell.CurrentRegion.Rows.Count
nCols = ActiveCell.CurrentRegion.Columns.Count
'
ReDim theData(nRows, nCols)
ReDim resultVector(2, 1)
'
' Grab the entire range and store in memory
'
theData = ActiveSheet.Range(ActiveSheet.Cells(1, 1), _
ActiveSheet.Cells(nRows, nCols)).Value
'
' Search through theData to find the name of interest...
'
j = 0
For i = 2 To nRows
If (StrComp(theName, theData(i, 5), vbBinaryCompare) = 0) Then
j = j + 1
ReDim Preserve resultVector(2, j)
resultVector(1, j) = i ' Save which row the item was on
resultVector(2, j) = theData(i, 2) ' Whatever is in Column B
End If
Next i
'
' Do something with the results in 'resultVector'...
'
End Sub

 

[Chip Pearson]

Ken,

Using the built-in Find function on a range is much faster than using
VBA to examine the contents of each cell. I have at
http://www.cpearson.com/excel/FindAll.aspx a function named FindAll
that searches a specified range for some value and returns a Range
object containing the cells in which the value was found. Using that,
you can easily get the reference to column A of the row of each found
cell. For example,


Dim R As Range
Dim FoundCells As Range
Set FoundCells = FindAll(Range("YourRange").Columns(5), "findwhat")
If FoundCells Is Nothing Then
Debug.Print "not found"
Else
For Each R In FoundCells
Debug.Print R.EntireRow.Cells(1,"A").Value
Next R
End If

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)