Converting to macro

  • Thread starter Thread starter angelo325
  • Start date Start date
A

angelo325

The following great bit of code converts time via a workbook event
from say 13.40 to 13:40.
Can any of you help me out with a macro incorporating this code that
converts data over a specific range repeated in numerous sheets?
I have tried but without success.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub

Thanks in advance.

Angelo
 
Use the Workbook_SheetChange() event, instead (put it in the
ThisWorkbook module):

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Not Intersect(Sh.Range("A1"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.Value = TimeSerial(Int(.Value), _
(.Value - Int(.Value)) * 100, 0)
.NumberFormat = "[h]:mm"
End With
Application.EnableEvents = True
End If
End Sub
 
Hi JEM
Thank you for replying - I really appreciate it.
Not sure why but I couldn't get the code to work.
Sorry if this is presumtious but a macro rather then Event is my
preferred option. Thanks though for replying - was at the end of all
hope etc.

Angelo
 
To convert numbers already in place it's almost the same:

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


Select the cells to convert and run the macro.
 
Thamks again JEM
Looking at the number of your postings I hesitate to ask you again. I
will not mind if you choose to let this go but on the chance that you
may be feeling generous.......

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?

A tall order I know so if this is asking a lot please don't reply -
I'll understand but still thank you for your previous excellent
postings.

Angelo














J.E. McGimpsey said:
To convert numbers already in place it's almost the same:

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


Select the cells to convert and run the macro.


Thank you for replying - I really appreciate it.
Not sure why but I couldn't get the code to work.
Sorry if this is presumtious but a macro rather then Event is my
preferred option. Thanks though for replying - was at the end of all
hope etc.
Click to expand...
Click to expand...
 
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).
 
Back
Top