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