Assistance with Sum Loop

[jim may]

I have set Rng3 = Visible Cells between A81 to A206 - I'm trying to loop through Rng3 and produce the Subtotals of the 3 employees (amts furnished) in Column E.
Looping drives me crazy...
Can someone provide the code to calculate and post the subtotals I need,,, PLEASE!!
TIA,
Jim

Excel Workbook
	A	B	C	D	E	F
1	FullName	Total Paid Hours	Department	Salaried?	Sub-Total Salaried
81	Bryant, John	3.42	Marketing	Yes
82	Bryant, John	2.72	Marketing	Yes
83	Bryant, John	5.37	Marketing	Yes
84	Bryant, John	2.18	Marketing	Yes
85	Bryant, John	5.57	Marketing	Yes
86	Bryant, John	9.53	Marketing	Yes
87	Bryant, John	8.00	Marketing	Yes	36.79	<<<
168	Gambee, Brandon	7.68	Deli	Yes
169	Gambee, Brandon	8.38	Deli	Yes
170	Gambee, Brandon	7.48	Deli	Yes
171	Gambee, Brandon	8.08	Deli	Yes
172	Gambee, Brandon	8.45	Deli	Yes
173	Gambee, Brandon	8.70	Deli	Yes	48.77	<<<
201	Greenman, Carleen	8.23	Office	Yes
202	Greenman, Carleen	8.57	Office	Yes
203	Greenman, Carleen	8.00	Office	Yes
204	Greenman, Carleen	8.17	Office	Yes
205	Greenman, Carleen	7.95	Office	Yes
206	Greenman, Carleen	8.18	Office	Yes	49.10	<<<
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D81	=IF(ISERROR(VLOOKUP(A81,SalEmploys,1,FALSE)),"","Yes")

Excel Workbook
Name	Refers To
SalEmploys	=LookUps!$I$4:$I$10
Workbook Defined Names

 

[JoeMo]

Hello Jim. This should do it, but untested so may need some tweaking:

Sub LoopAndSum()
Dim Rng3 As Range, sTot As Double
Set Rng3 = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible)
rws = Rng3.Rows.Count
Set Rng3 = Rng3.Offset(1, 0).Resize(rws - 1)
sTot = 0
With Rng3
    For i = 1 To .Rows.Count
        If .Cells(i, 1).Value = .Cells(i + 1, 1).Value Then
            sTot = sTot + .Cells(i, 2).Value
        Else
            .Cells(i + 1, 5).Value = sTot + .Cells(i, 2).Value
            sTot = 0
        End If
    Next i
End With
End Sub

 

[jim may]

JoMo - Thanks -- I'm playing around with your code (Chged Rng3 to Rng1), but...
Stepping thru the code (after line 4) in the immediate window I enter

? Rng1.address and get...
$A$81:$A$87,$A$168:$A$173,$A$201:$A$206

there are a total of 19 rows (7+6+6)

But the code rws = Rng3.Rows.Count
is producing 7

hummmmm..... What's wrong here?

Sub LoopAndSum()
Dim Rng1 As Range, sTot As Double
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Rng1 = Range("A2:A" & LR).SpecialCells(xlCellTypeVisible)

Thanks,

jim

 

[gregtx81]

Jim,

The Rows property of the Range object only applies to the first Area of a range. Here, because the cells aren't contiguous, there are multiple areas (each separated by a comma in the range's address).

This ought to give you the number of rows:

Intersect(Columns(1), Rng1).Cells.Count

 

[Peter_SSs]

Since Excel has a built-in Subtotals feature, I would try to let it do (at least most of) the work.

I have assumed that columns A and B do not contain formulas. If that is not the case, please give some more details as I would need to re-think the code.

Suggest testing in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Get_Totals()<br>    <SPAN style="color:#00007F">Dim</SPAN> Cel <SPAN style="color:#00007F">As</SPAN> Range<br><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Columns("A:B")<br>        .Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _<br>            Replace:=True, PageBreaks:=False, SummaryBelowData:=<SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cel <SPAN style="color:#00007F">In</SPAN> .SpecialCells(xlCellTypeFormulas)<br>            <SPAN style="color:#00007F">If</SPAN> Cel.Offset(, -1).Value <> "Grand Total" <SPAN style="color:#00007F">Then</SPAN><br>                Cel.Offset(-1, 3).Value = Cel.Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Cel<br>        .RemoveSubtotal<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[jim may]

Peter,

Thank you so Much

Your code is always "RITE-ON"

Jim