Run-time Error '1004' - VBA Help

[htran1984]

Hi All,

I'm trying to solve a problem with run-time error 1004: unable to get the Match property of the WorksheetFunction class for part of a macro below that was created by a coworker.

After some researching, it's advised that Application.Match should be used instead of Application.WorksheetFunction.Match. It's stated that Application.Match will return an error value if there's no match found & IsError can determine how to handle the error. I tried integrating Application.Match on my own but still got the same error.

I'm not sure the correct way to integrate Application.Match & IsError into existing code with the desired result, if no match then leave blank. Could you please advise a novice?

'Index to get Requestor Name
Range("B" & x).Value = Application.WorksheetFunction.Index(Sheets("Rostr").Range("A:T"), _
Application.WorsheetFunction.Match(ActiveSheet.Range("K" & x).Value, Sheets("Rostr").Range("B:B"), 0), 5)
If Range("B" & x).Value = "CMS Interface" Then
Range("B" & x).Value = "Corporate Citizenship"
End If


Thanks so much for your time & effort!

 

[steve the fish]

Try this

myMatch = Application.Match(Range("K" & x).Value, Sheets("Rostr").Range("B:B"), 0)
If Not IsError(myMatch) Then Range("B" & x).Value = Application.WorksheetFunction.Index(Sheets("Rostr").Range("E:E"), myMatch)

 

[htran1984]

I should've posted the entire section of the macro. I tried the suggested code but it lead to another unable to get the Match property of the WorksheetFunction class for a different part of macro.

Going off the suggested coding, I rewrote the error parts using same format and the entire macro seems to be working again.

'Index to get department & job level (Global Comps - ANLST)
myMatch = Application.Match(Range("K" & (x - 1)).Value, Sheets("Rostr").Range("B:B"), 0)
If Not IsError(myMatch) Then Range("B" & (x - 1)).Value = Application.WorksheetFunction.Index(Sheets("Rostr").Range("E:E"), myMatch)


'Index to get Requestor Name
myMatch = Application.Match(Range("K" & x).Value, Sheets("Rostr").Range("B:B"), 0)
If Not IsError(myMatch) Then Range("B" & x).Value = Application.WorksheetFunction.Index(Sheets("Rostr").Range("E:E"), myMatch)

'**********Calculate Requestor Data**********

'Input Requestor Portal IDs
    Range("K14").Activate
    For x = 1 To 10
        ActiveCell.Value = Sheets(ResortType & "Rankings").Range("A" & 2 + x)
        ActiveCell.Offset(2, 0).Activate
    Next

'Loop to get all indexed data
    For x = 14 To 33
        If Right((x / 2), 2) = ".5" Then
        'Index to get department & job level (Global Comps - ANLST)
        Range("B" & x).Value = Application.WorksheetFunction.Index(Sheets("Rostr").Range("A:T"), _
            Application.WorksheetFunction.Match(ActiveSheet.Range("K" & (x - 1)).Value, Sheets("Rostr").Range("B:B"), 0), 8)
            If Range("K" & (x - 1)).Value = "1MIKE001" Then
                Range("B" & x).Value = "SYS - Community Relations"
            End If
        Else
        'Index to get Requestor Name
        Range("B" & x).Value = Application.WorksheetFunction.Index(Sheets("Rostr").Range("A:T"), _
            Application.WorksheetFunction.Match(ActiveSheet.Range("K" & x).Value, Sheets("Rostr").Range("B:B"), 0), 5)
            If Range("B" & x).Value = "CMS Interface" Then
                Range("B" & x).Value = "Corporate Citizenship"
            End If
        'SumIfs to get MTD Tickets
        Range("C" & x).Value = Application.WorksheetFunction.SumIfs(Sheets(ResortType & "Request Details").Range("J:J"), _
            Sheets(ResortType & "Request Details").Range("B:B"), ActiveSheet.Range("K" & x).Value, _
            Sheets(ResortType & "Request Details").Range("AL:AL"), "N", _
            Sheets(ResortType & "Request Details").Range("AM:AM"), "N")
        'SumIfs to get YTD Tickets
            If ResortType = "" Then
            Range("F" & x).Value = Application.WorksheetFunction.SumIfs(Sheets("Request Details YTD").Range("J:J"), _
                Sheets("Request Details YTD").Range("B:B"), ActiveSheet.Range("K" & x).Value, _
                Sheets("Request Details YTD").Range("AL:AL"), "N", _
                Sheets("Request Details YTD").Range("AM:AM"), "N")
            Else
            Range("F" & x).Value = Application.WorksheetFunction.SumIfs(Sheets("Request Details YTD").Range("J:J"), _
                Sheets("Request Details YTD").Range("B:B"), ActiveSheet.Range("K" & x).Value, _
                Sheets("Request Details YTD").Range("AL:AL"), "N", _
                Sheets("Request Details YTD").Range("AM:AM"), "N", _
                Sheets("Request Details YTD").Range("P:P"), ResortType)
            End If
        End If
    Next