dellehurley
Board Regular
- Joined
- Sep 26, 2009
- Messages
- 171
- Office Version
- 365
- Platform
- Windows
Hi
I import information into an excel database from a csv file, the date column contains various formats like this...
I am trying to create a loop and use the isdate and cdate function. This way at least all the full dates are in date format but I cannot get it right. Please help.
I only need to extract the year however I don't think I can do this in one step so I am only dealing with the fulldates at this stage.
This is my code
The error is argument not optional
I import information into an excel database from a csv file, the date column contains various formats like this...
Data_Entry_Form_ver_25a.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | RIN | Surname, Given names | BirthDt | ||
2 | 2644 | Dobell, Glenn | 3 Apr 1893 | ||
3 | 2645 | Weston, Vivienne Doris | 1896 | ||
4 | 3996 | Thornton, | |||
5 | 3997 | Haydon, Hilton Bloomfield | 21-Jan-09 | ||
6 | 5607 | Humble, Alice Gloria | 21-May-11 | ||
7 | 5608 | Crowley, Annie | |||
8 | 5626 | McIntyre, John Edwin | 1862 | ||
9 | 5627 | Young, Florence Caroline | 1862 | ||
10 | 5777 | Capel, Clive Ross | 13 Mar 1894 | ||
11 | 6981 | Hurley, Daniel Patrick | 10 Mar 1875 | ||
12 | 6982 | Hurley, Jerome John | |||
13 | 7164 | Hammond, Neville | Abt. 1940 | ||
Sheet1 |
I am trying to create a loop and use the isdate and cdate function. This way at least all the full dates are in date format but I cannot get it right. Please help.
I only need to extract the year however I don't think I can do this in one step so I am only dealing with the fulldates at this stage.
This is my code
VBA Code:
Dim lRow_Dest As Long
Dim DateFormat As String
Dim i As Long
Dim DateSearch As Long
lRow_Dest = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(0).Row
DateFormat = IsDate.Value
DateSearch = Application.WorksheetFunction.CountIf(shImport.Range("C:C"), DateFormat)
If DateSearch >= True Then
For i = 2 To lDestLastRow
If shImport.Cells(i, 3) = DateFormat Then
Cells(i, 3).Value = CDate(Range(i, 3).Value)
End If
Next i
End If