CODEWITHSUNDEEP
excelvba
Stupid.Fat.Cat
Stupid.Fat.Cat

Reputation: 11345

Excel VBA Return the found row

Right now I have this:

        Range("B20:B60000").Select
        Selection.Find(What:=currentPerson, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate

Which selects a certain range of cells and finds for currentPerson (variable containing a person's name). How do I make it so that I can now use his cell as a reference and get the row above him?

Upvotes: 0

Views: 419

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

You can use following code:

Dim res As Range

Set res = Range("B20:B60000").Find(What:=currentPerson, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)

If Not res Is Nothing Then
    MsgBox "Address: " & res.Address
    MsgBox "Row: " & res.Row
    MsgBox "Cell above: " & Cells(res.Row - 1, res.Column).Address
    MsgBox "Entire row above: " & Cells(res.Row - 1, res.Column).EntireRow.Address
Else
    MsgBox "Nothing found"
End If

Upvotes: 3

Related Questions