none
Cannot capture row RRS feed

  • Question

  • Hi,
    By this
                With Worksheets("Sheet5").Range("A1:B10000")
                    Set c2 = .Find(Str0, LookIn:=xlValues)
                    ...


    I cannot capture the row, while relevant text does exist in B column, within the range.

    Many Thanks & Best Regards, Hua Min

    Monday, May 15, 2017 3:04 AM

Answers

  • Try using all of the parameters (arguments) in the Find line of code.

    Excel retains the last used values for some of the parameters in the current instance of Excel even if the last use of Find was in the interactive mode. Therefore, when writing code, it is always advisable to set all parameters except "After:=ActiveCell" unless the ActiveCell is definitely within the search range.

    You can use After if you specify the reference of the cell and the reference is within the search range like the following:

    After:=.Cells(.Cells.Count) 

    Setting the above parameter tells it to look after the last cell in the range so it loops around to the first cell of the range and therefore the next cell it looks at is the first cell of the range. If not used then the first cell that it looks at is actually the second cell of the range because it defaults to Next and if the first cell is a match then it is found last.

    See below example of code. (I like to use the line breaks and set it out in tabular format because I am a tabular person who finds it easier to read.)

    One common mistake is not re-setting the LookAt parameter. You need to know whether to use xlPart to find  anywhere in the cell or xlWhole if match is required for entire cell contents. This is one of the parameters that gets messed up if all parameters are not specified in the code and Excel uses the last setting when Find was used.

        Set c2 = .Find(What:=Str0, _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)


    Regards, OssieMac

    • Marked as answer by Jackson_1990 Tuesday, May 16, 2017 1:57 AM
    Monday, May 15, 2017 10:54 AM

All replies

  • Try using all of the parameters (arguments) in the Find line of code.

    Excel retains the last used values for some of the parameters in the current instance of Excel even if the last use of Find was in the interactive mode. Therefore, when writing code, it is always advisable to set all parameters except "After:=ActiveCell" unless the ActiveCell is definitely within the search range.

    You can use After if you specify the reference of the cell and the reference is within the search range like the following:

    After:=.Cells(.Cells.Count) 

    Setting the above parameter tells it to look after the last cell in the range so it loops around to the first cell of the range and therefore the next cell it looks at is the first cell of the range. If not used then the first cell that it looks at is actually the second cell of the range because it defaults to Next and if the first cell is a match then it is found last.

    See below example of code. (I like to use the line breaks and set it out in tabular format because I am a tabular person who finds it easier to read.)

    One common mistake is not re-setting the LookAt parameter. You need to know whether to use xlPart to find  anywhere in the cell or xlWhole if match is required for entire cell contents. This is one of the parameters that gets messed up if all parameters are not specified in the code and Excel uses the last setting when Find was used.

        Set c2 = .Find(What:=Str0, _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)


    Regards, OssieMac

    • Marked as answer by Jackson_1990 Tuesday, May 16, 2017 1:57 AM
    Monday, May 15, 2017 10:54 AM
  • To capture the row, you would use code like

    If Not c2 Is Nothing Then lngR = c2.Row

    Monday, May 15, 2017 3:43 PM