Most efficient way to search a range
 

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

Most efficient way to search a range
 

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


"Ken Warthen" wrote:

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

Most efficient way to search a range
 

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)



On Tue, 21 Apr 2009 08:25:01 -0700, Ken War then
wrote:

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