Run-time Error '1004' - VBA Help

htran1984

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. Windows
Try this

VBA Code:
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

New Member
Joined
Jan 27, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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)


VBA Code:
'**********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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,078
Messages
5,628,536
Members
416,323
Latest member
65563

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top