Match issue

[rollingzep]

Hi,

I do a Match between two workbooks. It works fine.
The field in first WB looks at Col A or Col B and returns values from col C of the second WB.
I now need a third condition. If there are no matches, then it should be "NA".
How to do this?

       Data = .Value
        With wbexcelEXT.Sheets("HQ").Cells(1).CurrentRegion
            For i = 2 To UBound(Data)
                Chk = Application.Match(Data(i, 33), .Columns(1), 0)
                If IsError(Chk) Then
                    Chk = Application.Match("*" & Data(i, 33) & "*", .Columns(2), 0)
                End If
                If Not IsError(Chk) Then Data(i, 55) = .Cells(Chk, 3)
            Next i
        End With
        .Value = Data
    End With
    ws.Range("BC:BC").NumberFormat = "@"

 

[rollingzep]

Sorry I had missing equal marks:

  Dim Chk1 As Range, Chk2 As Range

  With wbexcel.Sheets("TSS Fails").Cells(1).CurrentRegion.Resize(, 45)
    Data = .Value
        With wbexcelEXT.Sheets("HQLA").Cells(1).CurrentRegion
        For i = 2 To UBound(Data)
         Set Chk1 = .Columns(1).Find(what:=Data(i, 33), LookIn:=xlFormulas, lookat:=xlWhole)
         Set Chk2 = .Columns(2).Find(what:=Data(i, 33), LookIn:=xlFormulas, lookat:=xlWhole)
         If Not Chk1 Is Nothing Then
           Data(i, 55) = .Cells(Application.Match(Data(i, 33), .Columns(1), 0), 3)
         ElseIf Not Chk2 Is Nothing Then
           Data(i, 55) = .Cells(Application.Match(Data(i, 33), .Columns(2), 0), 3)
         Else
           Data(i, 55) = "NA"
         End If
        Next i
        End With
        .Value = Data
    End With

Thanks. That worked!

 

[Legacy 143009]

Glad it did work Thanks for the feedback