Archive of Mr Excel Message Board

Back to Excel VBA archive index
Back to archive home

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

Check out our Excel VBA Resources

Re: Search for text within a column

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

This archive is from the original message board at
All contents © 1998-2004
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.