I have data in an excel sheet in the following format:
ItemCode DeliveryDate 5456987 24.01.2009 5456988 5456989 12.24.2009 5456990 12/24/2009
I have stored the values of DeliveryDate in an array. I need to make decision on basics of date and then print the result in a new sheet. So I have to convert the values into array:
Dim current as Date, highest as Date, result() as Date
For Each itemDate in DeliveryDateArray
current = CDate(itemDate)
if current > highest then
highest = current
end if
' some more operations an put dates into result array
Next itemDate
'After activating final sheet...
Range("A1").Resize(UBound(result), 1).Value = Application.Transpose(result)
Unfortunately, CDate() function throws the error:
Run-time error '13':
Type mismatch
Is there a function in VBA which can:
Edit:
To reproduce the error, simply run
myDate = CDate("24.01.2009")
Looks like it could be throwing the error on the empty data row, have you tried to just make sure itemDate isn't empty before you run the CDate() function? I think this might be your problem.
I used this code:
ws.Range("A:A").FormulaR1C1 = "=DATEVALUE(RC[1])"
column A will be mm/dd/yyyy
RC[1] is column B, the TEXT string, eg, 01/30/12, THIS IS NOT DATE TYPE
Source: Stackoverflow.com