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


"Mini" wrote:

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