Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: What row is this string in?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

    Dim oCheRange as Range

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

    msgbox oCheRange.Row


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

    HTH

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •