Using Find on a range, and matching the offset

[davedave24]

Hello

I'm trying to find a string within a range, but only if the offset matches.

For example: I need to find the last occurence of, let's say, Cat 6.

COLUMN A      COLUMN B
Cat           1
Cat           6
Cat           1
Dog           4
Cat           6
Dog           3
Dog           8
Cat           2

My current code searches up from the bottom for the last instance of the string "Cat".

I need to adapt it so it checks if the Offset(0,1) = "6". If it does, we display some messagebox; if it doesn't, we continue with the Find upwards until we find Cat 6.

If I were searching for Cat 2, the code works, as it's the last entry in column A with "Cat"; I need the code to then continue upwards if the offset isn't what I'm looking for.

Here is my current code:

Dim sSerial As String
    Dim sDocNo As String
    Dim sCustomer As String
    Dim sOnHireDate As String
    Dim rngFind As Range
    
    On Error Resume Next
    
    sSerial = listOnHire.List(listOnHire.ListIndex, 1)
    
    With Sheets("Hire Diary").Range("D5:D5000")         'this is the range we are searching
        Set rngFind = .Find(What:=sSerial, _            'this searches upwards from the last cell in the range
                        After:=.Cells(1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False)
        If Not rngFind Is Nothing Then
            Application.Goto rngFind, False
                
            sDocNo = ActiveCell.Offset(0, -3)
            sCustomer = ActiveCell.Offset(0, -2)
            sOnHireDate = ActiveCell.Offset(0, 1)
                
            MsgBox "Serial : " & vbTab & vbTab & sSerial & vbCrLf & _
                    "Item : " & vbTab & vbTab & listOnHire.List(listOnHire.ListIndex, 0) & vbCrLf & _
                    "Doc No : " & vbTab & vbTab & sDocNo & vbCrLf & _
                    "Customer : " & vbTab & sCustomer & vbCrLf & _
                    "On Hire Date : " & vbTab & Format(sOnHireDate, "DD-MM-YY") & vbCrLf & _
                    "Expiry Date : " & vbTab & listOnHire.List(listOnHire.ListIndex, 2), vbOKOnly + vbInformation, "Item currently on-hire"

        Else
            MsgBox "Nothing found"
        End If
    End With

I have tried altering the line to say

if Not rngFind is Nothing and rngfind.offset(0,1) = whatever

but it produces the same problem that the code just stops on the last occurence and won't move upwards.

 

[SkipVought]

CODE
if Not rngFind is Nothing Then
   If rngfind.offset(0,1) = whatever Then
      'Do something here

   End If
End If

 

[PHV]

Have a look at the FindNext or FindPrevious methods of the Range object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

The Something else you could do is make a helper column with the concatenated value. Then do a direct MATCH() on that column. You wouldn't need any VBA! That's frankly how I'd approach it.

 

[davedave24]

Thanks, I used your idea and just had a column off to the right that concatenates them.

 

[Andrzejek]

I must be missing something obvious, but why not simply:

Dim strAnimal As String
Dim intColB As Integer
Dim intRow As Integer

strAnimal = "Cat"
intColB = 6

For intRow = 9 To 2 Step -1
    If Range("A" & intRow).Value = strAnimal And _
        Range("B" & intRow).Value = intColB Then
        
        MsgBox "Your info is in row " & intRow
        Exit For
    End If
Next intRow

Of course instead or hard-coded 9 you can detect the last row of your data other way.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.