Search for text within a column

Posted by Raj Nair on October 09, 2001 5:00 AM

i am searching for a ticker in a column of 18000 tickers, and am using the for - next loop, doing an if then else compare - cell by cell. the performance is slow. is there any way i could speed it up ?

regards, raj

Posted by Mark O'Brien on October 09, 2001 8:41 AM

Is a ticker like text or a number?

If so, use Excel's built in "Find" function. This works much faster than anything you will make in VBA.
Here's an example:

Put the text "Test" anywhere in Col A of Sheet1, then use this to find it:

Public Sub MyFind()
Dim c As Range
Set c = Worksheets("Sheet1").Range("A:A").Find("Test", LookIn:=xlValues)
MsgBox c.Address
End Sub