Range.FIND - find results in REVERSE ORDER

Question

O365 Excel (where have they hidden version numbers! Gosh!?)

I have a sheet with 2000+ rows and a column that contains...

2 pcs: ...  
3 pcs: ...  
10 pcs: ...  
...
<cursor-position>

I wish to FIND the PREVIOUS cell that has "pcs" in it,
so the search should go "backward" (up, toward lesser row number)
ignoring the current cell...

My code has FIND used like this:

ActiveCell.Columns("A:A").EntireColumn.Select     ' stay in current column

Selection.Find(What:="Pcs", LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _                ' go backwards
    MatchCase:=False, SearchFormat:=False).Activate

Currently this seems to impossible get going BACKWARDS.

(The idea here is to achieve a one time only re-work of the data,
duplicating the lines, to make there be 10 copies of lines with "10 pcs" stated)
Google searches turns up all but xlPrevious thingies.

---

An additional After:=ActiveCell, _ doesn't help...

still looking for an answer to this as I'd like to have MY code actually work; and secondarily to get to understand where the problem is.

NOTED: CTRL-F Dialog has no "Search backwards" option.

---

The answers below has shown that there are other (and better) ways to do this, what still remains is to UNDERSTAND why the behavior is as is.
My code is a slightly modified version of a macro recorded with "Use Relative references" set.

Answer 1

O365 Excel (where have they hidden version numbers! Gosh!?)

Currently this seems to impossible get going BACKWARDS.

For SearchDirection:=xlPrevious to work, you have to also use the argument After:=.

Try this. This code will find the previous match before cell A100

Option Explicit

Sub Sample()
    Dim aCell As Range
    Dim ws As Worksheet
    Dim rng As Range
    
    '~~> Change as applicable
    Set ws = Sheet1
    
    '~~> Change as applicable
    Set rng = ws.Range("A100")
    
    Set aCell = ws.Columns(1).Find(What:="Pcs", _
                                   LookIn:=xlValues, _
                                   After:=rng, _
                                   LookAt:=xlPart, _
                                   SearchDirection:=xlPrevious, _
                                   MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then MsgBox aCell.Address
End Sub

Here is an example

Option Explicit

Sub Sample()
    Dim aCell As Range
    Dim ws As Worksheet
    Dim rng As Range
    
    '~~> Change as applicable
    Set ws = Sheet1
    
    '~~> Change as applicable
    Set rng = ws.Range("K10")
    
    Set aCell = ws.Cells.Find(What:="Sid", _
                                   LookIn:=xlValues, _
                                   After:=rng, _
                                   LookAt:=xlPart, _
                                   SearchDirection:=xlPrevious, _
                                   MatchCase:=False, SearchFormat:=False)
    
    If Not aCell Is Nothing Then MsgBox aCell.Address
End Sub

Without the After:=rng, _, this is the result that I get.

Answer 2

Option Explicit

Sub InsertRows()

    Dim cel As Range, first As Range, col As Long
    Dim lastrow As Long, n As Long, t As Long
    
    Application.ScreenUpdating = False
    With ActiveSheet
        col = ActiveCell.Column
        lastrow = .Cells(.Rows.Count, col).End(xlUp).Row
        
        With .Cells(1, col).Resize(lastrow)
            Set cel = .Find(What:="Pcs", LookIn:=xlValues, _
               LookAt:=xlPart, SearchOrder:=xlByRows, _
               SearchDirection:=xlPrevious, _
               MatchCase:=False, SearchFormat:=False)
            If Not cel Is Nothing Then
                Set first = cel
                Do
                   n = Val(Split(cel, " ")(0)) ' 10 pcs
                   ' duplicate rows
                   If n > 1 Then
                      cel.EntireRow.Copy
                      cel.Offset(1, 1 - col).Resize(n - 1).Insert xlShiftDown
                      t = t + n - 1
                   End If
                   Set cel = .FindPrevious(cel)
                Loop While cel.Address <> first.Address
            End If
        End With
        
    End With
    Application.ScreenUpdating = True
    MsgBox t & " rows inserted", vbInformation
End Sub

Answer 3

Search Previous Using the Find Method

• The code covers the following:

I wish to FIND the PREVIOUS cell that has "pcs" in it, so the search should go "backward" (up, toward a lesser row number) ignoring the current cell...

• The bigger picture is covered in CDP1802's great code.

Usage

• Just select a cell in the column below the relevant data and run the code as many times as necessary. It will just select each matching cell and show a message box with its address.
• When getting tired of it, study the comments in the code.
• Finally, read through the text after the code, and you should have a much larger understanding of the Find method.

Sub TestFind()
    
    ' Check the active cell.
    If ActiveCell Is Nothing Then Exit Sub
    ' When? No visible workbooks open or the active sheet is not a worksheet.
    ' Note that the active cell exists even if you have selected a shape,
    ' while 'Selection' in this case is 'Nothing'.
    
    Dim rg As Range, IsInvalid As Boolean
    
    ' Reference the single-column range above the active cell,
    ' e.g. if the active cell is 'J20', then reference the range 'J1:J19'.
    With ActiveCell
        Dim cRow As Long: cRow = .Row
        Dim cCol As Long: cCol = .Column
        If cRow = 1 Then
            IsInvalid = True
        Else
            With .Worksheet
                ' The Find method will fail if the worksheet is filtered!
                If .FilterMode Then .ShowAllData
                Set rg = .Range(.Cells(1, cCol), .Cells(cRow - 1, cCol))
            End With
        End If
    End With
    
    If IsInvalid Then Exit Sub ' the active cell is in the 1st row
    
    Dim cell As Range
    
    ' Attempt to reference the cell closest to the active cell,
    ' i.e. search from the bottom to the top of the range.
    Set cell = rg.Find( _
        What:="pcs", _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchDirection:=xlPrevious)
    ' I prefer the following...
    'Set cell = rg.Find("pcs", rg.Cells(1), xlFormulas, xlPart, , xlPrevious)
    ' ... but I've done this hundreds of times
    
    If cell Is Nothing Then
        MsgBox "No match found.", vbExclamation
    Else
        ' Select the cell.
        cell.Select
        MsgBox "Found a match in cell """ & cell.Address(0, 0) & """.", _
            vbInformation
    End If
    
End Sub

The Find Method

• It is one of the most versatile, and therefore complex methods in VBA. Understanding it takes patience and time. There are people using VBA for years and still not understanding it entirely so don't be frustrated.
• It will fail if the worksheet is filtered. It will fail if there are hidden rows or columns and you use the xlValues parameter of the LookIn argument.
• It has 9 arguments, 7 of which I consider important. In the code, I have used only 4 of these 7. Here is why.

The Arguments (Important)

• 1.) What. Self-explanatory.
• 2.) After. Omitted. This is the most confusing argument. Its default parameter is the first cell of the range. Let's say our range is the range C2:C10. Then the default parameter is cell C2 which means, if the SearchDirection argument's parameter is set to xlPrevious, the search order will be C10, C9, ... C2 (which is needed in our case), while if the parameter is set to xlNext (or omitted), the search order will be C3, C4, ... C10, C2. Note that there is no typo, it's just what it is, the cell supplied to the After argument is the last cell searched. So if you want the order to be C2, C3, ... C10, you will have to set the cell to cell C10 usually in the form of rg.Cells(rg.Cells.Count). Focus on the word After when trying to figure out the logic behind it. Note that this becomes even more complex in a range with multiple rows and columns. Largely depends on the SearchOrder and SearchDirection arguments.
• 3.) LookIn. Its parameter's value is saved each time the Find method is called so it is best to always set it explicitly. It is preferable to use xlFormulas whenever possible because it will work even if rows or columns are hidden (not filtered). You won't use it if you're looking for partial matches in ranges containing formulas or when you want to ignore non-empty blank cells (e.g. =""). Then you'll have to switch to xlValues and make sure there are not hidden rows or columns. Additionally, there is an xlComments parameter which I've never used. Also, there is an xlFormulas2 parameter which I know nothing about except that the macro-recorder uses it probably in Microsoft 365 versions of Excel. But those two are irrelevant to our case.
• 4.) LookAt. Self-explanatory due to its two possible parameters: xlWhole and xlPart. Its parameter's value is saved each time the Find method is called so it is best to always set it explicitly.
• 5.) SearchOrder. Omitted. Irrelevant. Its parameter's value is saved each time the Find method is called so it is best to always set it explicitly. You don't have to use it in a single-column or single-row range. You gain nothing by supplying one or the other. Self-explanatory due to its two possible arguments: xlByRows and xlByColumns. Largely depends on the SearchDirection and After arguments.
• 6.) SearchDirection. Self-explanatory due to its default parameter xlNext, and its 2nd possible parameter xlPrevious. Largely depends on the SearchOrder and After arguments.
• 7.) MatchCase. Omitted. Its default parameter is False, while its 2nd possible parameter is True. Rarely you will need a case-sensitive search but when you'll need it, you'll know what to do.

The Arguments (Not Important)

• 8.) MatchByte. Used with Chinese languages. Saved each time the Find method is called. Never used it.
• 9.) SearchFormat. Omitted. Its default parameter is False, while its 2nd possible parameter is True. When you'll need it, google it.