U
Unbridled
I have a spreadsheet with some code to change text dates to true Excel-
formatted dates. Column B is what I am using for testing but would
like for this to work with any column containing date entries. I will
always start with row 2 of a date column to start the conversion
process since row 1 is my header row.
The code below works but with problem.
1. If there is not a date in column B, the calculation returns a
1/0/1900 value. I tried to correct this in the second If statement
but does not work.
I would like to do three things:
1. Be able to run this on any date column in the spreadsheet to the
end of the list
2. Leave blank cells blank
3. Optimize if possible
Any ideas appreciated.
*******************************************
Sub TextDateTest()
'The column calculating EOF() is column A which will always have a
record number
' with exception to blank records
'There are two sheets in this spreadsheet: ConExtract and data
Application.ScreenUpdating = False
Do
ActiveCell.Select
' This loop determines if same row in Column A is blank and
advances until the last record
If IsEmpty(ActiveCell.Offset(0, -1).Range("A1")) = False Then
Sheets("data").Select
Range("A1").Select
Selection.Copy
Sheets("ConExtract").Select
If IsEmpty(ActiveCell.Select) = False Then
'If this cell is empty this value blank , otherwise
01/0/1900 results
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Else
Exit Do
End If
Loop
End Sub
formatted dates. Column B is what I am using for testing but would
like for this to work with any column containing date entries. I will
always start with row 2 of a date column to start the conversion
process since row 1 is my header row.
The code below works but with problem.
1. If there is not a date in column B, the calculation returns a
1/0/1900 value. I tried to correct this in the second If statement
but does not work.
I would like to do three things:
1. Be able to run this on any date column in the spreadsheet to the
end of the list
2. Leave blank cells blank
3. Optimize if possible
Any ideas appreciated.
*******************************************
Sub TextDateTest()
'The column calculating EOF() is column A which will always have a
record number
' with exception to blank records
'There are two sheets in this spreadsheet: ConExtract and data
Application.ScreenUpdating = False
Do
ActiveCell.Select
' This loop determines if same row in Column A is blank and
advances until the last record
If IsEmpty(ActiveCell.Offset(0, -1).Range("A1")) = False Then
Sheets("data").Select
Range("A1").Select
Selection.Copy
Sheets("ConExtract").Select
If IsEmpty(ActiveCell.Select) = False Then
'If this cell is empty this value blank , otherwise
01/0/1900 results
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Else
Exit Do
End If
Loop
End Sub