Results 1 to 8 of 8

Thread: How find one or more instances of text in a worksheet and return row number?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jul 2007
    Location
    Berkeley, CA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How find one or more instances of text in a worksheet and return row number?

    Greetings.. I am trying to find occurrences of text (Airbillnbr) & corresponding row number(s) in an excel sheet but I am getting an error message when I run this code. The code must be defective because it's not finding the AirbillNbr like it should. My hope is to keep cycling through the FIND until I find the last instance of Airbill because there may be 0, 1 or n occurrences of AirbillNbr in the file. Thanks in advance for your help.

    I understand with the FIND statement you have to account for the nothing found situation as well or I may just find one occurrence and then after that 1 occurrence I'd show no more of that particular airbill and the nothing found case would apply after that too I guess.

    Set RangeLook = ActiveSheet.Range(Cells(TargetRow + 1, 1), Cells(MaxRowDOR, 1))
    Set FindResult = RangeLook.Find(What:=Airbillnbr, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If FindResult Is Nothing Then
    Windows(Airbills).Activate
    GoTo NextAirbillItem
    Else
    TargetRow = ActiveCell.Row
    Target = Cells(TargetRow, 1)
    Cells(TargetRow, 1).Select
    End If
    Last edited by ChetShannon; May 18th, 2019 at 04:24 PM.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,894
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    Code:
    Dim rwNum As Long
    rwNum = FindResult.Row
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Berkeley, CA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    Thanks for your response. Are you saying this is in addition to the code I have now?

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,894
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    I am saying that if you want to find the row number of the string you search for in the original code, that the snippet I posted would be the way to do it. It would have to be added into the same code as that in the OP, after the Find statement. The varoable 'rwNum' will return an integer number for the row where the string was found.
    Last edited by JLGWhiz; May 18th, 2019 at 05:19 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    Board Regular
    Join Date
    Jul 2007
    Location
    Berkeley, CA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    Thank you. Yeah the problem was that the code piece I pasted in was not working. I think there's a problem with the FIND statement I have. I'm actually understand how to get the row number OK once (and if) the FIND works but the FIND wasn't working ok. Sorry I didn't say that specificially.

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,894
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    I'm not sure that your RangeLook is set up to get the best results, but I also don't know exactly how you are initiating the macro, nor the circumstances that prompt a user to use the macro. But, the way that line of code is written, if the user selects a cell in the next to last row on the sheet, then the range in column A will only be one row. Is there a reason why you would not want to look at all of column A to find your search string?
    Last edited by JLGWhiz; May 18th, 2019 at 07:59 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Berkeley, CA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    Yeah my intent anyway was to have the lookup range start with 1:end of range and then after finding the first target text change the target range to be the row one(1) past where I found the current (target + 1) so Iíd be excluding the range I already looked at so as to not find the same target item again.

    Granted maybe that was overkill. Iím not sure. The pattern of what Iím doing is
    -start loop
    -find target text
    -gather data into variables
    -look for same target again
    -if reach end of data and no target found then get the next target and repeat steps above.
    -continue until last target is found or not found.
    Iím not super familiar with the .FIND statement hence my roughness on it..
    Thx. Chet

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,894
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: How find one or more instances of text in a worksheet and return row number?

    This uses your existing variables except for 'adr'. It will walk down column A finding each occurence of Airbillnbr, and the Message Box will display the row that it is found on. You can substitute something like rwNbr = FindResult.Row if you want to put the row number in a variable, or just use the FindResult.Row where you need the row number. It works either way. You will not duplicate the Find result with this method.

    Code:
    Dim adr As String 'add to your declaration at top of procedure
    With ActiveSheet
        Set .RangeLook = .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
        Set FindResult = RangeLook.Find(Airbillnbr, , xlValues, xlWhole)
            If Not FindResult Is Nothing Then
                adr = FindResult.Address
                Do
                    With Windows(Airbills) 'Is Airbills a variable?
                        MsgBox "Row number for found Item is " & FindResult.Row
                        'Do stuff
                    End With
                    Set FindResult = RangeLook.FindNext(FindResult)
                Loop While FindResult.Address <> adr
            End If
    End With
    I am not sure about the Windows(Airbills) syntax. Couldn't determine if Airbills is supposed to be a variable or a workbook name. You can handle that part.
    Last edited by JLGWhiz; May 19th, 2019 at 08:10 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

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
  •