Error 1004- Unable to get the matched property

[Murtuz]

Hi All,

Running below code and getting "1004" error "Unable to match the property".

I have below coding to match the headings from a sheet and paste the data in different sheet under same heading. but when it does not find exact match it get stuck at code "m = Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Headers").Range("A" & i).Value, Rng1, 0)"

I just need it to skip the mismatched heading and search for next column heading and paste the data if it exact match.

Kindly help!

Sub CopyHeaders()

LastRow1 = ThisWorkbook.Worksheets("Headers").Range("A" & Rows.Count).End(xlUp).Row
LastRow2 = ThisWorkbook.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow1

Set Rng1 = ThisWorkbook.Worksheets("Sheet1").Range("A1:AJ1")
Set Rng2 = ThisWorkbook.Worksheets("Sheet2").Range("A1:AJ1")

m = Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Headers").Range("A" & i).Value, Rng1, 0)
ThisWorkbook.Worksheets("Sheet1").Range("A2:A" & LastRow2).Columns(m).Copy


h = Application.WorksheetFunction.Match(ThisWorkbook.Worksheets("Headers").Range("A" & i).Value, Rng2, 0)

ThisWorkbook.Worksheets("Sheet2").Range("A2").Columns(h).PasteSpecial xlPasteValues

Next i

MsgBox "Data has been pasted successfully"

End Sub

 

[Alex Blakenburg]

If you use Application.Match instead of WorksheetFunction.Match you can use commands such as IfError and IsError, the worksheet function version forces you to rely on VBA error trapping.

Try something like this:
(you can dispense with the Else part since you want to do nothing OR reverse the logic and go with Exit For)

    With Application
        m = .IfError(.Match(ThisWorkbook.Worksheets("Headers").Range("A" & i).Value, Rng1, 0), 0)
    End With
    
    If m <> 0 Then
        Debug.Print "Do stuff"
    Else
        Debug.Print "Empty"
    End If