Error Encountered With Match VBA Line

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am getting an error with the line highlighted in red in my code below.

"Unable to get the match property of the WorsheetFunction class."

Rich (BB code):
Sub GUI_S_Submit1()
    Stop
    With ws_dsched
        'nd_yr = .Range("D2")
        'nd_month = Month("1" & .Range("E2"))
        'nd_day = .Range("F2")
        'nd_month = Month(.Range("G2"))
        'nd_day = Day(.Range("G2"))
        'n_date = DateSerial(nd_yr, nd_month, nd_day)
     
        'find row reference on master schedule
        drow = Application.WorksheetFunction.Match(n_date, ws_master.Columns(1), 0)
    End With
End Sub

n_date is returning a value of 2020-06-17. Column A of worksheet "ws_master" contains the value 2020-06-17 at row 185
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Have you tried ...
VBA Code:
        n_date = CDate(DateSerial(nd_yr, nd_month, nd_day))
End Sub
 
Upvote 0
I assume that your column contains dates not strings

It is more than likely down to data type
You have not declared your variables (- or you have deleted that from what you posted)
I suspect that your variable is defauting to type Date
When I forced n_date to be a DATE variable type (with Dim n_date As Date), VBA failed to find a match

In row 4 I put in a the date and formatted it like you have done
In row 5 I put in a date string that looks exactly the same
Unsurprisingly, the first message box returned 4, the second message box returned 5

VBA Code:
        Dim n_date As Long
        n_date = DateSerial(2020, 6, 17)       
        MsgBox Application.WorksheetFunction.Match(n_date, ws_master.Columns(1), 0)

        Dim X_date As String
        X_date = "2020-06-17"
        MsgBox Application.WorksheetFunction.Match(X_date, ws_master.Columns(1), 0)

Try declaring your variable like this
Dim n_date As Long

Let us know if the column contains strings rather than dates
 
Upvote 0
Hi GWteB, with your suggestion I opted to recreate 'n_date' (which had been created in other code) in this module.

Code:
Sub GUI_S_Submit1()
    Stop
    With ws_dsched
        nd_yr = .Range("D2")
        nd_month = DatePart("M", .Range("E2") & "/13/2016")
        nd_day = .Range("F2")
        n_date = CDate(DateSerial(nd_yr, nd_month, nd_day))
        
        'find row reference on master schedule
        drow = Application.WorksheetFunction.Match(n_date, ws_master.Columns(1), 0)
    End With
End Sub

With n_date being recreated, and the addition of CDate, I'm still getting the error. :(

If I add a countif line to my code (before the match line)
Code:
g = Application.WorksheetFunction.CountIf(ws_master.Columns(1), n_date)
, I get a value of 1 returned.
 
Upvote 0
Thank you Yongle, your solution came in as I was submitting my reply to GWteB.
Your solution worked, although I don't quite understand. Excel had no problem finding a "match" to carry out the countif function accurately, but couldn't match it with the match function lol.

Thank you both for stepping in to assist.
 
Upvote 0
The result of COUNTIF (count of nonempty cells based on comparison) is different from that of MATCH (relative position within matrix based on comparison). Regarding COUNTIF the formatting doesn't matter, regarding MATCH it sure does. It has to do with the way the comparisons are made. For a better understanding, Excel is not aware of date (and time), so it uses numbers to represent dates, but the presentation of dates can differ. YOU have to decide (in your code) whether a number is treated as a number or as a date. By explicit declaring your variables you are in control, Xl / VBA has to obey.
On the sheet below Xl is in control ...
Book1
ABCD
153
255
312-feb << 43873 - Adjusted D-MMM
412-2-2020 << 43873 - DATE
543873 << 43873 - DEFAULT
612-2-2020 << '12-2-2020 - TEXT
712-2-20 << 12-2-20 - TEXT
8john doe
9mary poppins
Blad2
Cell Formulas
RangeFormula
A1A1=MATCH(43873,C1:C20)
B1B1=MATCH(43873,C3:C20)
A2A2=COUNTIF(C1:C20,43873)
B2B2=COUNTIF(C3:C20,43873)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top