hi i'm trying to turn all cells in

[daveonion]

hi i'm trying to turn all cells in column d into Proper format, however my code isn't working, see below
any help would be appreciated,
ps
first of all i used different code to go through the whole sheet (see below first, only slightly different), however it did strange things to my date fields any explanations why would be appreciated.

Sub ChangeToProper()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In Columns("D&quot
c.Value = WorksheetFunction.Proper(c.Value)
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Sub ChangeToProper()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In ActiveSheet.UsedRange
c.Value = WorksheetFunction.Proper(c.Value)
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[xlbo]

Let me guess - date fields were converted to a number yes ???
This number is actually the serial number that represents the number of days from Jan 01 1900 that the date is. ALL dates in M$ products are based on serial numbers. Any time you see a date like dd/mm/yy, you are just seeing the serial number FORMATTED to look like that

So, to stop your code clearing the formats of date cells, try this

Sub ChangeToProper()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each c In ActiveSheet.UsedRange
if isdate(c) then
else
c.Value = WorksheetFunction.Proper(c.Value)
end if
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[daveonion]

Geoff thanks for your speedy response and also thanks for your solution, works fine now

 

[combo]

If Geoff's guess concerning dates is not right:

- in your first sub the reference to cells is bot full, should be [tt]For Each c In Columns("D&quot.Cells[/tt]

- in your second sub - you refer to ActiveSheet object. If the avtive sheet is not what you expect - strange things can happen. Try instead ThisWorkbook.Worksheets(1) or similar direct reference to sheet you want to change.

combo

 

[daveonion]

thanks combo, both worked fine