Using VBA to total hours for emloyees based on values in different column

[jerryneubauer]

I need to fix my VBA. Currently it is:

Range("M6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-12]=R[1]C[-12],,SUM(R[-4]C[-1]:RC[-1]))"
Selection.AutoFill Destination:=Range("M6:M599"), Type:=xlFillDefault


Col. A = Name
Col. L = Adj. Time
Col. M = Total Time

Formula in Column M:
=IF(A11=A12,,SUM(L7:L11))


I need to add up the adjusted time in Col. L for each employee, and put the total on the last line of that employee in col. M. The number of rows for each employee can vary, but they are always shown together.

Right now the formula merely see when the name changes, and adds 5 rows. I need to only add the rows that apply to each employee.



[TABLE="width: 273"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Adj. Time[/TD]
[TD]Total Time[/TD]
[/TR]
[TR]
[TD]Alvarez, Hector M[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Alvarez, Hector M[/TD]
[TD="align: right"]10.75[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Alvarez, Hector M[/TD]
[TD="align: right"]8.75[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Alvarez, Hector M[/TD]
[TD="align: right"]10.50[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Alvarez, Hector M[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Alvarez, Hector M[/TD]
[TD="align: right"]8.50[/TD]
[TD="align: right"]48.00[/TD]
[/TR]
[TR]
[TD]Amari, Raymond D[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amari, Raymond D[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amari, Raymond D[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Amari, Raymond D[/TD]
[TD="align: right"]9.50[/TD]
[TD="align: right"]25.75[/TD]
[/TR]
[TR]
[TD]Anderson, Jermaine D[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Anderson, Jermaine D[/TD]
[TD="align: right"]8.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Anderson, Jermaine D[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Anderson, Jermaine D[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD]Anderson, Jermaine D[/TD]
[TD="align: right"]8.25[/TD]
[TD="align: right"]32.75[/TD]
[/TR]
[TR]
[TD]end[/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG09Mar57
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("L2", Range("L" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 2) = Dn.Offset(, 1)
       .Add Dn.Value, Dn.Offset(, 2)
    [COLOR="Navy"]Else[/COLOR]
        Dn.Offset(, 2) = .Item(Dn.Value) + Dn.Offset(, 1).Value
        .Item(Dn.Value).Value = 0
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 2)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jerryneubauer]

Thanks for your response.

This doesn't seem to work.

The result is "0" in col. N, when I need the result to show in Col. M, and that result is the total hours (adj. time - Col "L") for each employee.

The total time for each employee should be:
Alvarez - 56.50
Amari - 25.75
Anderson - 40.75

That result should appear on the last line for each employee, in Col. M.

Also, what would this look like in a formula that appears on each line in Col. M?

Thanks again.

 

[MickG]

Assuming the actual names are in column "K" with result in "M"
Try changing this line as shown:-

From this:-

[COLOR=#000080]Set[/COLOR] Rng = Range("L2", Range("L" & Rows.Count).End(xlUp))

To this:-

[COLOR=#000080]Set[/COLOR] Rng = Range("K2", Range("K" & Rows.Count).End(xlUp))

 

[jerryneubauer]

I appreciate your interest.

I modified your suggestion to adjust for the names starting in A5, because that is where the names start:
Set Rng = Range("a5", Range("a" & Rows.Count).End(xlUp)).

This had no effect. The formula has to look at names starting with "A5", and then total the hours for the name starting with "L5". The result goes to Col. "M", on the last row for that person.

As I have it now, this is the formula that resides in every cell in Col. "M" starting with M5: =IF(A5=A6,,SUM(L1:L5))
It only adds up the previous 5 lines when a name changes, regardless of how many lines are actually needed for that Name.

Thanks again.

 

[MickG]

I've just seen in your original post:-
Col. A = Name
Col. L = Adj. Time
Col. M = Total Time

Results in column "M"

[COLOR="Navy"]Sub[/COLOR] MG10Mar02
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 12) = Dn.Offset(, 11)
       .Add Dn.Value, Dn.Offset(, 12)
    [COLOR="Navy"]Else[/COLOR]
        Dn.Offset(, 12) = .Item(Dn.Value) + Dn.Offset(, 11).Value
        .Item(Dn.Value).Value = 0
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Dn.Offset(, 12)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jerryneubauer]

BINGO! One minor adjustment. Thanks! On to the next problem.

 

[MickG]

Sorry about that, glad you have it working !!