Error 0438 while using MATCH

[tadhg_mac_nuadat]

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]

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]

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

[tadhg_mac_nuadat]

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]

Try:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

[tadhg_mac_nuadat]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

[tadhg_mac_nuadat]

Sorry here you go!

[MrShorty]

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]

Works as well on my side with CLng & DateValue :

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]

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