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

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/

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

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

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?

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