Select cells by format with VBA

C

Clive Smith

I'm trying to write a macro to convert 1904 dates to 1900
and vice versa (by adding or subtracting 1462 as
appropriate). That's quite simple but as I want the whole
worksheet processed at once I want the macro to examine
the format of each cell and make the change only where it
has a date format. Otherwise other data will be affected.
That's beyond my ability. Can anyone help me?
 
B

Bernie Deitrick

Clive,

Try the macros below, which should change all the dates on the activesheet.

HTH,
Bernie
MS Excel MVP

Sub AddToDates()
Dim myCell As Range
For Each myCell In ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeConstants)
If InStr(1, myCell.Value, "/") > 0 Then
myCell.Value = myCell.Value + 1462
End If
Next
End Sub

Sub SubtractFromDates()
Dim myCell As Range
For Each myCell In ActiveSheet.UsedRange. _
SpecialCells(xlCellTypeConstants)
If InStr(1, myCell.Value, "/") > 0 Then
myCell.Value = myCell.Value - 1462
End If
Next
End Sub
 
B

Bernie Deitrick

What I should have added that it will change dates that are formatted with
/'s. You will need to modify the sub if your date format uses -'s.

Sorry about that,
Bernie
MS Excel MVP
 
S

Sandy V

I think this should work (but pls correct me otherwise):

If IsDate(myCell) Then

Regards,
Sandy
 
Top