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 subive 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.........
Just curious... Why are you using this:
For c = 2 To 1000
Dim cont As String
cont = c
crit = ActiveSheet.Range("e" + cont).Valueinstead 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
