MATCH command does not work with dates

[Mini]

Hi,
I have 2 excel worksheets assuming "sheet1" and "sheet2".
example:
Sheet1:
user date1 date2 date3 date4 date5...dateN
AA
BB
CC

Sheet2:

user data date1 date2 date3 date4 date5 dateN
AA total 100 102.............................................
AA mean 30 32..............................................
AA Average 31 35................................................
CC
total........................................................................
CC mean.....................................................................
CC average...................................................................


I want to match the user and date between the 2 sheets and copy the value
"mean" from sheet2 to sheet1 if match found.

The code below does not return any error but no data was copied over
eventhough I can see the match. After some tries, I found that it happened
because of the 2nd match (matching the date) does not return any match
although the dates are exactly the same in both sheets. My dates are in the
following format: MM/DD/YY. When I put an apostrophe in front of the dates
to convert them to text then everything worked fine. However, I don't want
to convert my dates to text but prefer to keep them as dates.
Could anyone help me to resolve the issue with matching date.
Thanks a lot,
Minnie

With Sheets("sheet1")
For i = 2 To lastRow
With .Range("B" & i)
X = Application.Match(.Value,
Sheets("sheet2").Range("A2:A10000"), 0)
If IsNumeric(X) Then
For j = 2 To lastColumn
With Cells(1, j)
temp = Application.Match(.Value,
Sheets("sheet2").Range("C1:Z1"), 0)

If IsNumeric(temp) Then
'MsgBox (temp)
Sheets("sheet2").Cells(X + 1, 2 + temp).Copy
Destination:=Sheets("sheet1").Cells(i, 1 + j)
End If
End With
Next j
End If
End With
Next i
End With

 

[Matthew Herbert]

Mini,

I'm sure that plenty of people have run into this same sort of issue, so
know that it is common. What you have to remember is that a date is really
an integer value. So, try explicitly converting the date to a Long and then
use the Long as the lookup value in the match. I've provided an example
below (which prints the results to the Immediate Window). If the type
conversion doesn't work for you then post back because there are other ways
to work around this.

Best,

Matthew Herbert

Assumptions:
A1: 1/1/2010
B1: <blank>
C1: 12/30/2009
D1: 12/31/2009
E1: 1/1/2010
F1: 1/2/2010
G1: 1/3/2010

Sub IllustrateMatchDates()
Dim rngLkupVal As Range
Dim rngLkupArr As Range
Dim varMatch As Variant

Set rngLkupVal = Range("A1")
Set rngLkupArr = Range("C1:G1")

varMatch = Application.Match(rngLkupVal.Value, rngLkupArr, 0)
Debug.Print "Date format => error:"; varMatch

varMatch = Application.Match(CLng(rngLkupVal.Value), rngLkupArr, 0)
Debug.Print "Explicit type conversion => no error:"; varMatch

End Sub

 

[michdenis]

Hi,

You should replace ".Value" by the property ".Value2" of a range
Or by using Clng(cdate(.Value)) instead of .Value in these 2
lines of coce
X = Application.Match(.Value, Sheets("sheet2").Range("A2:A10000"), 0)
temp = Application.Match(.Value, Sheets("sheet2").Range("C1:Z1"), 0)

'----------------------------------------------------
Dim Sh As Worksheet
Dim Sh2 As Worksheet

Set Sh = Sheets("sheet1")
Set Sh2 = Sheets("sheet2")

With Sh
For i = 2 To lastRow
With .Range("B" & i)
X = Application.Match(CLng(CDate(.Value)), Sh2.Range("A2:A10000"), 0)
If IsNumeric(X) Then
For j = 2 To lastColumn
With Sh.Cells(1, j)
temp = Application.Match(CLng(CDate(.Value)), Sh2.Range("C1:Z1"), 0)
If IsNumeric(temp) Then
'MsgBox (temp)
Sh2.Cells(X + 1, 2 + temp).Copy Destination:=Sh.Cells(i, 1 + j)
End If
End With
Next j
End If
End With
Next i
End With
'----------------------------------------------------




"Mini" <[email protected]> a écrit dans le message de groupe de discussion :
(e-mail address removed)...
Hi,
I have 2 excel worksheets assuming "sheet1" and "sheet2".
example:
Sheet1:
user date1 date2 date3 date4 date5...dateN
AA
BB
CC

Sheet2:

user data date1 date2 date3 date4 date5 dateN
AA total 100 102.............................................
AA mean 30 32..............................................
AA Average 31 35................................................
CC
total........................................................................
CC mean.....................................................................
CC average...................................................................


I want to match the user and date between the 2 sheets and copy the value
"mean" from sheet2 to sheet1 if match found.

The code below does not return any error but no data was copied over
eventhough I can see the match. After some tries, I found that it happened
because of the 2nd match (matching the date) does not return any match
although the dates are exactly the same in both sheets. My dates are in the
following format: MM/DD/YY. When I put an apostrophe in front of the dates
to convert them to text then everything worked fine. However, I don't want
to convert my dates to text but prefer to keep them as dates.
Could anyone help me to resolve the issue with matching date.
Thanks a lot,
Minnie

With Sheets("sheet1")
For i = 2 To lastRow
With .Range("B" & i)
X = Application.Match(.Value,
Sheets("sheet2").Range("A2:A10000"), 0)
If IsNumeric(X) Then
For j = 2 To lastColumn
With Cells(1, j)
temp = Application.Match(.Value,
Sheets("sheet2").Range("C1:Z1"), 0)

If IsNumeric(temp) Then
'MsgBox (temp)
Sheets("sheet2").Cells(X + 1, 2 + temp).Copy
Destination:=Sheets("sheet1").Cells(i, 1 + j)
End If
End With
Next j
End If
End With
Next i
End With

 

[ker_01]

As Matthew stated, dates are stored as integers. However, just because you
see a date in the cell, that doesn't mean your cell contents is an integer,
just that the cell is formatted to only show the date.

If your date (on either sheet) is coming from a report or other system,
consider that it might have date /and/ time information, even if the cell is
formatted to only show the date. So if you try to match 40204 (Jan 26, 2010)
against 40204.58 (Jan 26, 2010 at 1:53PM) then you won't get a match, even
though both show the same thing in the user view (Jan 26, 2010). If this is
the case in your data, you may want to rounddown all of your values to get a
date with zero minutes, after which you should be able to make a match (all
else being equal).

To test for this, format values on both sheets as a number (instead of a
date) and see if any of them have decimal values.

HTH,
Keith