excel VBA minutes and hours

May 12, 2009 at 13:39:30
Specs: Windows Vista, pentium 4 processor

hi i need some help ive got some code which works for whole numbers and decimals however when i put in something like 1.30 for 1 hour 30 it will only add 1.30 onto the total

the code ive got checks a column for a certain word then adds the numbers in column I can anyone help me edit the code so it applies to hours and minutes

this is my code

Private Sub CommandButton2_Click() ''hours button
Sheets("main sheet").Select
Dim a As Double
Dim crit As String
a = 0
For c = 2 To 1000
Dim cont As String
cont = c
crit = ActiveSheet.Range("e" + cont).Value
If crit = "car" Then
a = a + ActiveSheet.Range("j" + cont).Value ' adds them
End If
Next c
Sheets("PAC sheet").Select
Range("h2").Value = a ' print answer in h2
end sub

ive tried formatting the cells but nothing seems to work


See More: excel VBA minutes and hours

Report •


#1
May 12, 2009 at 18:30:42

ive tried formatting the cells but nothing seems to work

You will need a Custom Format

On the Task Bar,
Select Format
Select Cells
Select Custom (Bottom of List)
On the right side, scroll down until you find

[H]:MM:SS

Click OK

It must be the format as shown above with the square brackets.........

MIKE

http://www.skeptic.com/


Report •

#2
May 12, 2009 at 20:35:49

Just curious...

Why are you using this:

For c = 2 To 1000
Dim cont As String
cont = c
crit = ActiveSheet.Range("e" + cont).Value

instead of this?

For c = 2 To 1000
crit = ActiveSheet.Range("e" & c).Value


P.S. I can't see your entire project, but typically you don't have to Select a sheet to perform an action on it. You can refer to the sheet and range directly in your code.

Perhaps you can use this syntax, eliminating the .Select lines.

crit = Sheets("main sheet").Range("e" & c).Value

and

Sheets("PAC sheet").Range("h2").Value = a

It's very possible that your code can be reduced to this:

Private Sub CommandButton2_Click() ''hours button
Dim a As Double
  For c = 2 To 10
   If Sheets("main sheet").Range("e" & c) = "car" Then
    a = a + Sheets("main sheet").Range("j" & c) ' adds them
   End If
  Next c
 Sheets("PAC sheet").Range("h2") = a ' print answer in h2
End Sub


Report •

#3
May 13, 2009 at 08:08:43

Thanxs for all your help ive got this to work now :)

Report •

Related Solutions

#4
May 13, 2009 at 08:54:04

I'm not sure that you can get Excel to consider 1.30 as 1 hour 30 minutes, unless you do some text parsing like this:

=TIMEVALUE(LEFT(1.3,1)&":"&RIGHT(1.3,1)&0)

1.3 formatted as [h]:mm:ss is going to be seen by Excel as 1.3 days and return 24 + 7 hours and 12 minutes, or 31:12.

Why aren't you using 1:30 for 1 hour 30 minutes?


Report •

#5
May 16, 2009 at 11:21:22

i got it working :) thankyou everyone for your help i formated the cells with the [h]:mm and used my original code to add them up instead of decimal point it works used : so 1 hour 30 = 01:30
Thankyou

Report •


Ask Question