Parsing a number in a cell

[Kevalson]

I have an Excel sheet where I have to keep track of overtime.
The report I receive in Excel has all the hours worked in the cell.
Example.

Column A heading (A1) reads "Regular Hours".
Column B heading (B1) reads "Overtime Hours".
Cell A2 under column A shows the hours as 48, and cell B2 under column B shows 0.
It is supposed to be 40 under A2 and 8 for B2.
Basically it is a bug in the program that exports the time.

So instead I would like to add a button in a new blank Excel sheet. (This I know how to do)
Then I can just copy and paste from the report into the new Excel sheet. (This I know how to do also)
Then I want to click the button that will take the 48 in A2, change it to 40, and put the remainder of 8 into B2.

Now if the regular hours is less than 40, then I want it to leave it alone.
Sometimes the hours are 36 in A2, so obviously there is no overtime anyway for B2.
Now there is about 75 rows of time, so it would need to go and do this for all 75 rows.

So, long story short:

Need it to change the 48 to 40, and put the remainder in the next cell which is B2 in this case.

I hope this makes sense to everyone.

 

[Gerald Higgins]

What I think I would do is use helper columns containing formulas, such as
=MIN(48,A1)
and
=MAX(A1-48,0)

where A1 contains the initial time of 49 hours or whatever.
There are other ways of writing this type of formula as well.

There are also various ways of either getting VBA to either do the whole calculation for you, OR getting VBA to write these formulas into the spreadsheet, do the calculation, copy the results into the required places, and then delete the formulas.

 

[SteveO59L]

something along the lines of

Dim lr As Long
Dim c As Range, rng
lr = Range("a" & Rows.Count).End(xlUp).Row
Set rng = Range("a2:a" & lr)
For Each c In rng
If c.Value > 40 Then
c.Offset(0, 1) = c.Value - 40
c.Value = 40
End If
Next c

 

[FormR]

Hi, here is some another code based option you can try.

Sub SplitOutOvertime()
With Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
  .Offset(, 1).Value = Evaluate("INDEX(IF(" & .Address & ">40," & .Address & "-40,0),0)")
  .Value = Evaluate("INDEX(IF(" & .Address & ">40,40," & .Address & "),0)")
End With
End Sub

 

[Kevalson]

Thanks everyone.
Had some help from a coworker and this is what we came up with.

Dim overtime As Integer
Sub Macro1()
'
' Macro1 Macro
'

'
Range("A1").Select
While ActiveCell.Value <> ""
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
If ActiveCell.Value > 40 Then
overtime = ActiveCell.Value - 40
Cells(ActiveCell.Row, ActiveCell.Column + 1) = overtime
ActiveCell.Value = 40
End If
Wend

End Sub

 

[FormR]

Hi, you might want to also try out the other suggestions - it's rarely necessary to select cells to work with them and it's often a slower approach.

 

[Kevalson]

Thank you. I will do that.