Hi Mick,

Am Sun, 3 Jul 2011 15:46:57 +1000 schrieb Vacuum Sealed:

With Column ("N")
1. Need to change format to a number. (copy to temp location)
2. Trim the 5 Leading numbers leaving only the decimal portion
3. Paste values from temp location bank to ("N")
4. change format to match Column("M")

try:

With ActiveSheet
LRow = .Cells(.Rows.Count, "N").End(xlUp).Row
'write decimal part to column Z
'change 2 to 1 if there's no header
.[Z2].Formula = "=MOD(N2,1)"
.[Z2].AutoFill .Range("Z2:Z" & LRow)

.Range("Z2:Z" & LRow).Copy
.Range("N2").PasteSpecial xlPasteValues
.Range("N2:N" & LRow).NumberFormat = "hh:mm:ss AM/PM"
.Range("Z2:Z" & LRow).ClearContents
End With

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2