If the text is in its own cell or cells, it can be avoided by
testing for a numeric value in the cell and ignoring any cells that
aren't numeric. If it's mixed in the same cell as the times you want
to convert, it's a bit trickier.

By adding an optional range argument, the macro will work with
either the Selection or the specified range:

Public Sub ConvertDecimalTimesToTimes(Optional rng As Range)
Dim cell As Range
If rng Is Nothing Then Set rng = Selection
For Each cell In rng
With cell
If Not IsEmpty(.Value) Then
If IsNumeric(.Value) Then
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End If
End If
End With
Next cell
End Sub

Note that adding an optional argument means that the macro will no
longer show up in the Macro list when you select Tools/Macro/Macros,
but you can still type in the name (you may want to make it a bit
shorter, in that case).


In article ,
(angelo325) wrote:

1. Some of the data contained words or letters so I got a Type
MisMatch error 13. Can this be catered for? A quick search on Google
showed this was a common problem but I have played around with your
code for so long I just haven't got the will to sort it out tonight.
Give me an engine block any day!

2. I wanted to call this Macro after completion of another in which a
column of data is copied from another workbook into different named
sheets. Because my knowledge of VB is zero how can I set the range
just copied in preparation for ConvertDecimalTimesToTimes and then
move onto the next?