Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Most efficient way to search a range | Excel Programming | |||
most efficient large range summing? | Excel Programming | |||
more efficient way to lookup a range? | Excel Discussion (Misc queries) | |||
Efficient STRING search with SpecialCells | Excel Programming | |||
Efficient Search / Find | Excel Programming |