Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select cells by format with VBA
Many thanks
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locked Several Worksheets, allow format cells/select unlocked cell | Excel Discussion (Misc queries) | |||
Up down arrow keys do not select cells if select locked cells unch | Excel Discussion (Misc queries) | |||
how do you "select locked cells" w/o "select unlocked cells"? | Excel Discussion (Misc queries) | |||
Select format cells Custom but Saves As Specil Why. | Excel Worksheet Functions | |||
When I select "format cells", the format dialog box does not disp. | Excel Worksheet Functions |