CODEWITHSUNDEEP
excelvba
TBoulz
TBoulz

Reputation: 351

Find Cell Range Based on Two Criteria

I've put together some VBA to find the last row with a certain criterion that matches the current value in my loop, then take action. This VBA code works, until I realized that the worksheet can contain the matching value multiple times but with different dates in another column. So I'm now trying to add a second search criterion to my VBA.

Here is the snippet of VBA as of now.

   For Each t In trans.Cells
            On Error GoTo NxtT2
                If t.Value = Empty Then
                On Error GoTo 0
                    ty = t.Offset(0, -3).Value
                    tx = t.Offset(0, -6).Value
                    Set searchTerm = .Range("E:E")
                    Set where = searchTerm.Find(what:=ty, after:=searchTerm(1), searchdirection:=xlPrevious)
                        If t.Offset(0, -3).Value = where.Value And IsError(where.Offset(0, 3).Value) Then
                            t.Value = "#N/A"
                        End If
                End If

NxtT:
    On Error GoTo 0
    If t.Offset(1, -3).Value = "" Then Exit For
    Next t

NxtT2:
    Resume NxtT

Basically what I'm trying to do is make the line Set where = searchTerm.Find(what:=ty, after:=searchTerm(1), searchdirection:=xlPrevious) to also include the txvalue along with the ty that is already in there.

Something like this, if possible?

Set where = searchTerm.Find(what:=ty & tx, after:=searchTerm(1), searchdirection:=xlPrevious)

But I know that is not the correct syntax for it.

Any advice on how to approach this in the simplest way?

Upvotes: 0

Views: 65

Answers (1)

FreeMan
FreeMan

Reputation: 5687

Not an answer to the original question, but to the issue I created with my off-the-cuff code review.

Your error handling never properly wrapped up. The code still thought it was in the error handler because you "exited" the error handler with the Next, which you really can't do - you need to leave this "instance" of error handling with a Resume.

Give this a shot instead for the cleaned up error handling.

NOTE: I declared variables because I've got Option Explicit set, which you also should also have. I've made the brash assumption that you've got your variables declared outside the code you shared. Use the variables as you've declared them, not as my quickie patched Variant declarations.

Sub foo()
  Dim t As Variant
  Dim ty As Variant
  Dim tx As Variant
  For Each t In Cells
    On Error GoTo ErrorHandler
    If t.Value = Empty Then
      On Error GoTo 0
      ty = t.Offset(0, -3).Value
      tx = t.Offset(0, -6).Value
      Dim searchterm As Range
      Set searchterm = .Range("E:E")
      Dim where As Range
      Set where = searchterm.Find(what:=ty, after:=searchterm(1), SearchDirection:=xlPrevious)
      If t.Offset(0, -3).Value = where.Value And IsError(where.Offset(0, 3).Value) Then
        t.Value = "#N/A"
      End If
    End If

Continue:
  Next 

CleanExit:
  Exit Sub

ErrorHandler:
    If t.Offset(1, -3).Value = "" Then
      Resume CleanExit
    Else
      Resume Continue
    End If

End Sub

Upvotes: 1

Related Questions