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 = "@"

 

[Legacy 143009]

Usually my practice in such cases is:

  Dim Chk As Range

  Set Chk = .Columns(1).Find(what:Data(i, 33), LookIn:=xlFormulas, lookat:=xlWhole)
  If Not Chk Is Nothing Then
    Data(i, 55) = .Cells(Application.Match(Data(i, 33), .Columns(1), 0), 3)
  Else
    'Do something here if no matches
  End If

 

[rollingzep]

Usually my practice is in such cases:

  Dim Chk As Range

  Set Chk = .Columns(1).Find(what:Data(i, 33), LookIn:=xlFormulas, lookat:=xlWhole)
  If Not Chk Is Nothing Then
    Data(i, 55) = .Cells(Application.Match(Data(i, 33), .Columns(1), 0), 3)
  End If

Thanks. But I do need to add the value "NA", if there is no match between 33 of WB 1 and col1 and col2.
I need a condition like, Data(i, 55) = "NA"
The analysts will look for these NA and fill the correct value.

 

[Legacy 143009]

How about this:

  Dim Chk1 As Range, Chk2 As Range

  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

 

[rollingzep]

How about this:

  Dim Chk1 As Range, Chk2 As Range

  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

I get syntax error on the Set line

    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)
       [B]Set Chk = .Columns(1).Find(what:Data(i, 33), LookIn:=xlFormulas, lookat:=xlWhole)[/B]
        If Not Chk Is Nothing Then
          Data(i, 55) = .Cells(Application.Match(Data(i, 33), .Columns(1), 0), 3)
        Else
          Data(i, 55) = "NA"
        End If
            Next i
        End With
        .Value = Data
    End With

 

[Legacy 143009]

Have you declared Chk1 and Chk2 as Range?

 

[rollingzep]

Have you declared Chk1 and Chk2 as Range?

yes

 

[Legacy 143009]

  Dim Chk1 As Range, Dim 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

 

[rollingzep]

  Dim Chk1 As Range, Dim Chk2 As Range

  With wbexcel.Sheets("TSS Fails").Cells(1).CurrentRegion.Resize(, 55)
    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

I am still getting syntax errors on the two Set line of codes.
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)
I have the following references

 

[Legacy 143009]

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