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

[ChetShannon]

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

 

[JLGWhiz]

Dim rwNum As Long
rwNum = FindResult.Row

 

[ChetShannon]

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

 

[JLGWhiz]

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.

 

[ChetShannon]

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.

 

[JLGWhiz]

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?

 

[ChetShannon]

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

 

[JLGWhiz]

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.

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.