What row is this string in?

[jrnyman]

What is the best way to pass the row number where a string can be found to a variable?

Example, in Column A I have a list of countries. I want CntryRow to equal the row where CHE is found. I've been relying on the Find method, but it seems to slow things down on a grand scale because I end up with code like:

Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Find(What:="CHE").Activate

I've tried combining the last two lines, but it does not always work, and I cannot use it to on an inactive sheet.

Any suggestions?

 

[Mark O'Brien]

On 2002-04-18 04:23, jrnyman wrote:
What is the best way to pass the row number where a string can be found to a variable?

Example, in Column A I have a list of countries. I want CntryRow to equal the row where CHE is found. I've been relying on the Find method, but it seems to slow things down on a grand scale because I end up with code like:

Sheets("Sheet1").Select
Columns("A:A").Select
Selection.Find(What:="CHE").Activate

I've tried combining the last two lines, but it does not always work, and I cannot use it to on an inactive sheet.

Any suggestions?

<pre>
Dim oCheRange as Range

Set oCheRange = Sheets("Sheet1").Columns("A:A").Find(What:="CHE")

msgbox oCheRange.Row</pre>

This code will only work if "CHE" exists in Column A.

HTH