Search Range for cell that begins with "Pri"

[scrabtree23]

The ActiveCell is A100. I need VBA or a formula, that will start with Cell A99 and keeps looking in each next cell above until it arrives at the first cell directly above A100 thats content begins with the letters "Pri". Take into account, there could be no cell that begins with "Pri", so help me avoid an "error".

[xladept]

Try this:

Sub Scabtree(): Dim r As Long
         r = 99:    Do
    If LCase(Left(Cells(r, 1), 3)) = "pri" Then _
        Cells(r, 1).Select: Exit Sub
            r = r - 1: Loop Until r = 0
End Sub

[scrabtree23]

Thanks. I just gave A100 as an example. The active cell could be any cell in column A. Sorry for the trouble/lack of clarity.

[Marc L]

As a starter :

Sub Demo1()
           Dim Rg As Range
    If ActiveCell.Column = 1 Then
           Set Rg = Range("A1", ActiveCell).Find("Pri*", ActiveCell, xlValues, xlWhole, , xlPrevious)
        If Not Rg Is Nothing Then Rg.Select: Set Rg = Nothing
    End If
End Sub 

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[protonLeah]

Option Explicit
Sub Scabtree()
    Dim SelCel      As Range, _
        PRILOC      As Variant, _
        SearchRng   As Range
    
    'Option 1
    Set SelCel = ActiveCell
    
    'Option 2
    'Set SelCel = Cells(Rows.Count, "A").End(xlUp)
         
    Set SearchRng = Range("A1", SelCel)
    
    With SearchRng
        Set PRILOC = .Find("pri*", _
            after:=SelCel, _
            LookIn:=xlValues, _
            SearchDirection:=xlPrevious, _
            MatchCase:=False)
    End With 'searchrng
        
    If Not PRILOC Is Nothing Then
        MsgBox "-pri- found at: " & PRILOC.Address
    Else
        MsgBox "-pri- not found"
    End If
End Sub

[Marc L]

Thanks for the rep' !

[xladept]

Thanks for the rep!