The following code gives an integer answer representing
minutes:

For Each C In .Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
End With
Next

Problem: when the value is 59, I cannot find a 'nice' way to display as
hours and minutes (eg 62 should display
as 1:02)

but I am using this:
If .Value 59 And .Value < 120 Then
i = .Value - 60
If i < 10 Then
.NumberFormat = "General"
strA = "1:0" & i
C.Value = strA
Else
.NumberFormat = "General"
strA = "1:" & i
C.Value = strA
End If
End If

Is there a better way, please?

Regards.

Hi

Where to start... Time is the decimal part of datetime -and your first line
of code hides decimals.

Please do some reading at
http://www.cpearson.com/excel/datetime.htm#SerialDates
and suddenly this will make some sense:

Sub test()
MsgBox Format(62 / 24 / 60, "hh:mm:ss")
End Sub

Also, it's not obvious what your code is supposed to do, so suggesting
improvements is very difficult.

HTH. Best wishes Harald

"Stuart" skrev i melding
...
The following code gives an integer answer representing
minutes:

For Each C In .Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
End With
Next

Problem: when the value is 59, I cannot find a 'nice' way to display as
hours and minutes (eg 62 should display
as 1:02)

but I am using this:
If .Value 59 And .Value < 120 Then
i = .Value - 60
If i < 10 Then
.NumberFormat = "General"
strA = "1:0" & i
C.Value = strA
Else
.NumberFormat = "General"
strA = "1:" & i
C.Value = strA
End If
End If

Is there a better way, please?

Regards.

Sub cc()
Dim hr As Long
Dim mn As Long
For Each C In Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
If .Value 60 Then
hr = Int(.Value / 60)
mn = .Value Mod 60
.Value = "'" & hr & ":" & Format(mn, "00")
.NumberFormat = "General"
End If
End With

Next
End Sub

--
Regards,
Tom Ogilvy



"Stuart" wrote in message
...
The following code gives an integer answer representing
minutes:

For Each C In .Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
End With
Next

Problem: when the value is 59, I cannot find a 'nice' way to display as
hours and minutes (eg 62 should display
as 1:02)

but I am using this:
If .Value 59 And .Value < 120 Then
i = .Value - 60
If i < 10 Then
.NumberFormat = "General"
strA = "1:0" & i
C.Value = strA
Else
.NumberFormat = "General"
strA = "1:" & i
C.Value = strA
End If
End If

Is there a better way, please?

Regards.

Thanks for that, and apologies to Harald for my brief
post.
As background, here is what a local Car Club wanted
for a Rally next weekend:

An A4 printout(s) that would clearly show the time it takes to travel a set
distance, at a constant speed.
The parameters given we D (distance) from 0.01
miles to 3 miles (in 0.01 mile increments) and 15 to 45
mph in 1 mile increments.

It soon became clear that if you tried to read this printout
in the heat of a Rally, you'd be confused. So I was asked
just to show minutes (as an Integer) up to 59 minutes, and then hours and
minutes when the calculated value was
greater.

I could not find a way to do this using Time/hh:mm:ss
formats. So I came up with this code, which sets up the
'table' on an empty sheet and then calculates the time
elapsed:

Sub NewGetTimeInMinutes()
Dim C As Range, i As Integer, j As Double
Dim hr As Long, mn As Long

With ActiveWorkbook.Sheets("Sheet4")
.Columns("A").ColumnWidth = 11
With Range("A3")
.Value = "MPH"
End With
With Range("A4")
.Value = "DISTANCE"
End With
'set the MPH
i = 15
For Each C In .Range("B3:AF3")
With C
.Value = i
End With
i = i + 1
Next
'set the Distance travelled
j = 0.01
For Each C In .Range("A5:A304")
With C
.Value = j
End With
j = j + 0.01
Next
'calculate Elapsed time
'courtesy of Tom Ogilvy 4/04/05
For Each C In Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
If .Value 60 Then
hr = Int(.Value / 60)
mn = .Value Mod 60
.Value = "'" & hr & ":" & Format(mn, "00")
.NumberFormat = "General"
End If
.HorizontalAlignment = xlRight
End With
Next
.Range("A3").HorizontalAlignment = xlLeft
End With
End Sub

Now all I've got to do is to sort out the presentation
(how many pages, font etc).
Thanks for the help.

Regards.

"Tom Ogilvy" wrote in message
...
Sub cc()
Dim hr As Long
Dim mn As Long
For Each C In Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
If .Value 60 Then
hr = Int(.Value / 60)
mn = .Value Mod 60
.Value = "'" & hr & ":" & Format(mn, "00")
.NumberFormat = "General"
End If
End With

Next
End Sub

--
Regards,
Tom Ogilvy



"Stuart" wrote in message
...
The following code gives an integer answer representing
minutes:

For Each C In .Range("B5:AF310")
With C
.NumberFormat = "0"
.Value = (Range("A" & C.Row).Value * 60) / _
(Cells(3, C.Column).Value / 60)
.Value = Round(C.Value, 0)
End With
Next

Problem: when the value is 59, I cannot find a 'nice' way to display as
hours and minutes (eg 62 should display
as 1:02)

but I am using this:
If .Value 59 And .Value < 120 Then
i = .Value - 60
If i < 10 Then
.NumberFormat = "General"
strA = "1:0" & i
C.Value = strA
Else
.NumberFormat = "General"
strA = "1:" & i
C.Value = strA
End If
End If

Is there a better way, please?

Regards.