problem with dates

[Alex G]

Hi,
how can i verify if a value is a date or a numeric befor copy it to a
target cell in order to change the target cell number format
accordingly.

for an example:
I need to copy a value from cell a1 to cell a2.

I do not know what will be the value in cell a1 (it can be a date or a
number)and i want to adjust the a2 cell's number format according to
the type data in cell a1 in order to view the value in cell a2 in its
correct format.

when i copy the value from a1 to a2, if the value in a1 is a number
(i.e. 1234) the value that excel will show in cell a2 will also be
1234. but if there is a date value in cell a1 (i.e 01/01/2002), excel
will show in cell a2 the long value of the date (i.e. in cell a2 we
will see the value 37257 and not as it should be - 01/01/2002).
looking for your kind reply. thanks
Alex g

 

[Rob van Gelder]

Alex,

You should investigate IsDate and IsNumeric VBA functions.

Sub testit()
With Range("A1")
If IsDate(.Value) Then
MsgBox "Date"
ElseIf IsNumeric(.Value) Then
MsgBox "Numeric"
Else
MsgBox "Not date or numeric"
End If
End With
End Sub

Rob

 

[Tom Ogilvy]

Range("A2").Value = Range("A1").Value
Range("A2").NumberFormat = Range("A1").Numberformat