Select cells by format with VBA
 

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?

Select cells by format with VBA
 

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


"Clive Smith" wrote in message
...
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?

Select cells by format with VBA
 

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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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


"Clive Smith" wrote in message
...
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?

Select cells by format with VBA
 

Many thanks

Select cells by format with VBA
 

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

If IsDate(myCell) Then

Regards,
Sandy

Bernie Deitrick wrote:
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

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...

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


"Clive Smith" wrote in message
...

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?