MATCH function in VBA for dates

M

mdarby

New Member
Joined
Nov 15, 2003
Messages
16
I am trying to use the MATCH function in VBA to search for a particular date in a range containing dates and return the matching row number. I can accomplish this in the Excel worksheet but get errors when implementing it in Excel ("type mismatch" I think.)

Several posts have alluded to Application.WorksheetFunction.Match not working properly in VBA when used with dates. Is this in fact true, or is there a workaround? I get the impression that the problem might be whether the dates are seen as unformated (eg. 38675) or formated (1/5/2002). Is there a way to make sure VBA sees both the lookup data and the date range in the same way?

I currently have to perform the calculation in some scratch cells and read back the result into VBA. But it would be cleaner to understand if it can be performed in VBA itself.

Thanks, and aplogies if this has been resolved earlier.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
This code works for me;

Code: 
Sub test_match()
Dim myvalue As Long
myvalue = WorksheetFunction.Match("North", Range("A8:A11"), 0)
MsgBox (myvalue)
End Sub
Book1
ABCD
8East10Formula
9South203
10North30
11West40
Sheet1
 
Upvote 0
Jimboy,

Thanks for your reply. I too can make the funciton work for numeric or text data as in your example. I am wondering if there is a persistent problem, however, using DATES.

Thanks
 
Upvote 0
Hi,

Use

Application.Match

rather than
Application.WorksheetFunction.Match
or
WorksheetFunction.Match

it seems to produce better results. MATCH in VBA is quirky.

For your specific question, in the Match function, coerce the values to dates or to Longs.


myvalue = Application.Match(CDate(your date), Range("A8:A11"), 0)
or
myvalue = Application.Match(CLng(your date), Range("A8:A11"), 0)

One of those should work.
 
Upvote 0
Jay,

Thanks SO MUCH for your solution!!! It was very, very frustrating. Using CLng did the trick.

Regards,
Michael
 
Upvote 0
Oh! This is an old post but I do have problem in solving this. I have a column of dates (column A) and the column B is the quantity (number ) respect to the dates. Now, I need to sum the total for each month. I would like to use the 'match' to find the location of last cell for each month so that I can use 'sum' to sum from the begining to the end of each month.
However, I found nothing using the following codes:
Dim res As Variant
res = Application.Match(CDate(1 / 8 / 2008), Worksheets("Year").Range("a1:a30"), 0)
If IsError(res) Then
MsgBox "not found"
Else
MsgBox "found at pos: " & res
End If

Can anyone tell me where the problem is or tell me the smarter way to accomplish the task? thanks
 
Upvote 0
One more thing. When I am not using VBA, in the other words, I type this in a cell:

=match(date(2008,2,2), A1:A10)

the result comes out! but

compile error:
expected )

is shown with 2008 highlighted when I run the program.
 
Upvote 0
Hi,

I've only been playing around in VBA for a few days so can't really suggest much but try using #1/8/2008# instead of CDate(1/8/2008) and see if this makes a difference. I think this triggers something in Excel to use the value inside the # as a date (although I'm making an assumption there). I've been having a few date issues I've been scratching my head with myself.

Chris
 
Upvote 0
I've been working on a similar problem, and have found that either using Dim mydate as Long, or using the CLng suggestion above makes the Match function to work properly.

Seems like this is because Match in VBA is meant to use values (in this case date serial numbers) and gets tripped up when you pass formatted numbers. So I'm having success forcing Long while running the functions, then only Dim'ing the end result/output as a Date at the very end.

Hope this is a) correct and b) helpful
 
Upvote 0
My problem is very similar to these.

I am getting Type mismatch error for Tracker_Col even though I know it will return the number 13.

A5 in the Column Numbers sheet = "MS-000 P"

Dim Tracker_Col As Integer

Sheets("Column Numbers").Select
Tracker_Col = Application.Match(A5, Worksheets("SC").Range("A4:GH4"), 0)

Any ideas?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VBA script for checking date, matching date, and publishing data in corresponding cell
Replies
1
Views
290
kshealy
K
L
  • Question Question
VBA Match function for two criteria / columns
Replies
9
Views
226
Domenic
D
D
  • Solved
VBA Conditional Statement That Functions Based on Today's Date
2
Replies
18
Views
819
Cubist
Cubist
M
  • Solved
VBA Date issue
Replies
3
Views
644
dmt32
D
Drrellik
  • Solved
VBA to complete Index/Match with 2 criteria
2
Replies
10
Views
470
Alex Blakenburg
A

Forum statistics

Threads
1,224,507
Messages
6,179,183
Members
452,893
Latest member
denay

Share this page

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