Error 0438 while using MATCH

**tadhg_mac_nuadat** · 08-25-2022, 09:53 AM

Hi,

I'm writing a little macro for our school HR department to calculate FMLA days.

Please Login or Register  to view this content.

I'm trying to get the user to just type in the date (MM/DD/YY) instead of their typing in the cell the date is found in the spreadsheet, like B28. So I was hoping to use "Application.Match(Application.DateValue(beginLeave), Range("B1:B366"), 0)" to return the row where the date is found and concatenate with the appropriate column letter, so I can reuse my existing code. However, it's throwing error 438 ("object doesn't support this property or method.") Could anyone please try to tell me why? thank you!!

**nimrod1313** · 08-25-2022, 10:38 AM

Application.Match is not valid . it's Application.WorksheetFunction.Match

ATTENTION : I've been corrected in that my post is incorrect ... please ignore ..

Peer review is so important ... makes us all smarter

**Marc L** · 08-25-2022, 10:43 AM

Wrong ! As Application.Match is valid, WorkSheetFunction was added just in order to crash VBA if an error is met ...

**tadhg_mac_nuadat** · 08-25-2022, 10:52 AM

Originally Posted by nimrod1313

Application.Match is not valid . it's Application.WorksheetFunction.Match

Also not you fix this you may get a message of "Unable to get the Match property " ... this will occur if match is not found
... you may want some error trapping for that scenario like this ..

Please Login or Register  to view this content.

if this has helped please consider clicking "add reputation" ..

Thanks, but adding WorksheetFunction didn't change anything as Marc said. Still getting same error. Could it have something to do with the user inputting the date as a string and its not being interpreted correctly?

**CheeseSandwich** · 08-25-2022, 10:54 AM

Try:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

**tadhg_mac_nuadat** · 08-25-2022, 10:57 AM

Originally Posted by CheeseSandwich

Try:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

Many thanks but that just threw error 13 (type mismatch) and Error 2042 when I printed the output to the console.

**Marc L** · 08-25-2022, 11:01 AM

As the date should be entered according to the Windows Regional settings in order to be recognized as a valid date
so easy to check with VBA function IsDate then can be converted via CDate as a true Excel date ...

**CheeseSandwich** · 08-25-2022, 11:02 AM

The way i see it the string value was not being converted to a numeric value - meaning Excel does not see it as a date.

When i print 'DateValue(CDate(beginLeave))' i get a numeric value which means that Excel can understand it as a date.

Are the date values you hold in column B dates or are they just text, you can test by adding a formula in column C like =B2+1, see if you get an error or if the date increments by one day.

**Marc L** · 08-25-2022, 11:05 AM

Another way as the date entry is in an Excel inner original format whatever the Regional Settings could be
is to directly allocate the text date to a cell via the Formula property then Excel converts it automatically to a real date ...

**tadhg_mac_nuadat** · 08-25-2022, 11:12 AM

Originally Posted by CheeseSandwich

The way i see it the string value was not being converted to a numeric value - meaning Excel does not see it as a date.

When i print 'DateValue(CDate(beginLeave))' i get a numeric value which means that Excel can understand it as a date.

Are the date values you hold in column B dates or are they just text, you can test by adding a formula in column C like =B2+1, see if you get an error or if the date increments by one day.

Thank you, everything in Column B is formatted as dates. I tried printing "DateValue(CDate(beginLeave))" and that gave me an overflow error.

**nimrod1313** · 08-25-2022, 11:13 AM

Wrong ! As Application.Match is valid, WorkSheetFunction was added just in order to crash VBA if an error is met

I stand corrected ... thanks for the diplomatic put education

**tadhg_mac_nuadat** · 08-25-2022, 11:14 AM

Originally Posted by Marc L

As the date should be entered according to the Windows Regional settings in order to be recognized as a valid date
so easy to check with VBA function IsDate then can be converted via CDate as a true Excel date ...

Thank you, not sure entirely what you mean as I'm not an IT guy . . . just trying to help out HR as they are even more clueless than me. I am using Excel for Mac though; not sure if this makes a difference? They will be running the macro on Windows PCs though....

**Marc L** · 08-25-2022, 11:21 AM

As a reminder Application.Match works in conjunction only with a Variant variable so easy to check via IsError & IsNumeric VBA functions
without the need of an useless On Error Resume Next ...

You should first check what returns Debug.Print IsDate(beginLeave) ?

**tadhg_mac_nuadat** · 08-25-2022, 11:24 AM

Originally Posted by Marc L

As a reminder Application.Match works in conjunction only with a Variant variable so easy to check via IsError & IsNumeric VBA functions
without the need of an useless On Error Resume Next ...

You should first check what returns Debug.Print IsDate(beginLeave) ?

Returns true!!

**Marc L** · 08-25-2022, 11:29 AM

So the text date can be converted to a real date via the VBA function CDate.
Then use a Variant variable to store the Application.Match result and check it via IsError or IsNumeric VBA function, that's all folks !

**tadhg_mac_nuadat** · 08-25-2022, 11:42 AM

I tried to store the Application.Match result and got an overflow error... here's what I did, I probably screwed up

Please Login or Register  to view this content.

**Marc L** · 08-25-2022, 12:24 PM

Attach at least a workbook, better for helpers than any guessing challenge ...

**tadhg_mac_nuadat** · 08-25-2022, 12:25 PM

Sorry here you go!

**MrShorty** · 08-25-2022, 12:57 PM

One thing I find is sometimes important to remember when Excel and VBA are talking to each other is that Excel only knows 2 data types: double precision float and text string. VBA, on the other hand, knows many other data types like currency and date. Therefore, the MATCH() function, being an Excel function, only knows 2 data types (double and string), and does not know what to do with a date data type, so it returns an error.

I changed the statement to use a double:

Please Login or Register  to view this content.

(I'm not sure if this is always necessary, but it seemed necessary to first convert the text input to date before converting to double. CDbl(beginLeave) returned a type mismatch error suggesting that VBA could not figure out how to go from string direct to double). That seemed to allow the MATCH() function to return the expected row number for the date I would enter.

When VBA is doing everything only using VBA functions, then you can take advantage of all of VBA's different data types. Sometimes, when I am using Excel functions or when VBA and Excel are talking to each other, I find it important to remember that Excel and its functions only know two data types, so it can be important to make sure that VBA is also using only those two data types.

**Marc L** · 08-25-2022, 01:04 PM

Works as well on my side with CLng & DateValue :

PHP Code:


Sub Demo1()

    Dim V

        V = Application.InputBox(prompt:="When does the employee's maternity leave begin? Answer must be in MM/DD/YY format.", Type:=2)

        If Not IsDate(V) Then Beep: Exit Sub

        V = Application.Match(CLng(DateValue(V)), [Calendar!B1:B366], 0)

        If IsError(V) Then Beep: Exit Sub

        Debug.Print V

End Sub

**tadhg_mac_nuadat** · 08-25-2022, 02:05 PM

Thank you so much, Marc. I think CLng did the trick! No more errors of any sort!

Error 0438 while using MATCH

LinkBack

Thread Tools

Rate This Thread

Display

Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Re: Error 0438 while using MATCH

Thread Information

Users Browsing this Thread

Similar Threads

Index/Match Formula with nested error test that has an error that is unsolvable!

Error 1004 Unable to get the Match property (and On Error Resume Next doesn't help!)

[SOLVED] Index,Match,Match based on date Range - error in some cells

2D Index match match & error trapping

unable to get match property error in userform derived index match

[SOLVED] .Match with On Error: On Error Resume Next works. On Error GoTo only works once. Err.Clear

[SOLVED] Vlookup to Pivot Table, using Match function, returns error if can't find match value

Bookmarks

Bookmarks

Posting Permissions