Help with Looping Code

I have a couple hundred lines of Check Amts and Check Numbers (as text)
like below..
I need to Loop from bottom to top and Sum the previous amts applicable
to
The same check number. Bottom line ColA should = 12660; 2 rows up
should = 1,583.96, etc up to Row 2.
Can someone get me started?
TIA,

ColA ColB
197.11 65704
65704
78.03 65705
65705
27.85 65706
1691.84 65706
122.98 65706
2275.13 65706
289.95 65706
65706
372.22 65707
57.96 65707
921.05 65707
232.73 65707
65707
12660 65709
65709

Hi Jim,

Try Something like:

'=============>>
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim rCell As Range
Dim MySum As Double
Dim CalcMode As Long
Dim ViewMode As Long
Const FirstCol As String = "A" '<<==== CHANGE

Set WB = Workbooks("YourBook.xls") '<<==== CHANGE
Set SH = WB.Sheets("Sheet1") '<<==== CHANGE

Set rng = SH.Range(Firstcol & 1).CurrentRegion
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)

On Error GoTo XIT
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveWindow
ViewMode = .View
.View = xlNormalView
End With

SH.DisplayPageBreaks = False

rng.Columns(2).Insert

For Each rCell In rng.Columns(3).Cells
With rCell
.Select
MySum = MySum + .Offset(0, -2).Value
If .Value <> .Offset(1).Value Then
.Offset(0, -1).Value = MySum
MySum = 0
End If
End With
Next rCell

XIT:
With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

ActiveWindow.View = ViewMode

End Sub
'<<=============


---
Regards,
Norman



"JimMay" <jmay@cox.net> wrote in message
news:cILgg.40329$fG3.21866@dukeread09...
>I have a couple hundred lines of Check Amts and Check Numbers (as text)
> like below..
> I need to Loop from bottom to top and Sum the previous amts applicable
> to
> The same check number. Bottom line ColA should = 12660; 2 rows up
> should = 1,583.96, etc up to Row 2.
> Can someone get me started?
> TIA,
>
> ColA ColB
> 197.11 65704
> 65704
> 78.03 65705
> 65705
> 27.85 65706
> 1691.84 65706
> 122.98 65706
> 2275.13 65706
> 289.95 65706
> 65706
> 372.22 65707
> 57.96 65707
> 921.05 65707
> 232.73 65707
> 65707
> 12660 65709
> 65709
>

The blank cells in column A are already there:

Dim rng as Range, ar as Range, rng1 as Range
set rng = Columns("A").specialcells(xlBlanks)
for each ar in rng.Areas
set rng1 = ar.offset(ar.Rows.count).Resize(1,1)
rng1.Formula = "=Subtotal(9," & ar.Address(0,0) & ")"
Next


--
Regards,
Tom Ogilvy



"JimMay" <jmay@cox.net> wrote in message
news:cILgg.40329$fG3.21866@dukeread09...
> I have a couple hundred lines of Check Amts and Check Numbers (as text)
> like below..
> I need to Loop from bottom to top and Sum the previous amts applicable
> to
> The same check number. Bottom line ColA should = 12660; 2 rows up
> should = 1,583.96, etc up to Row 2.
> Can someone get me started?
> TIA,
>
> ColA ColB
> 197.11 65704
> 65704
> 78.03 65705
> 65705
> 27.85 65706
> 1691.84 65706
> 122.98 65706
> 2275.13 65706
> 289.95 65706
> 65706
> 372.22 65707
> 57.96 65707
> 921.05 65707
> 232.73 65707
> 65707
> 12660 65709
> 65709
>

Thanks Tom; I'm on a roll...

Now that I can get (from immediate window):
? rng.Address
$A$3,$A$5,$A$11,$A$16,$A$18

How can I derive the variables --
(Where A2 is the starting point)
A3-A2 = 1 << number of rows to include in Subtotal)
A5-A3+1 = 1
A11-A5+1= 5
A16-A11+1 = 4
A18-A16+1 = 1

Appreciate all your help..
I'm getting there..
Jim


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:#AGJCfEiGHA.1264@TK2MSFTNGP05.phx.gbl:

> The blank cells in column A are already there:
>
> Dim rng as Range, ar as Range, rng1 as Range
> set rng = Columns("A").specialcells(xlBlanks)
> for each ar in rng.Areas
> set rng1 = ar.offset(ar.Rows.count).Resize(1,1)
> rng1.Formula = "=Subtotal(9," & ar.Address(0,0) & ")"
> Next
>
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "JimMay" <jmay@cox.net> wrote in message
> news:cILgg.40329$fG3.21866@dukeread09...
>
> > I have a couple hundred lines of Check Amts and Check Numbers (as text)
> > like below..
> > I need to Loop from bottom to top and Sum the previous amts applicable
> > to
> > The same check number. Bottom line ColA should = 12660; 2 rows up
> > should = 1,583.96, etc up to Row 2.
> > Can someone get me started?
> > TIA,
> >
> > ColA ColB
> > 197.11 65704
> > 65704
> > 78.03 65705
> > 65705
> > 27.85 65706
> > 1691.84 65706
> > 122.98 65706
> > 2275.13 65706
> > 289.95 65706
> > 65706
> > 372.22 65707
> > 57.96 65707
> > 921.05 65707
> > 232.73 65707
> > 65707
> > 12660 65709
> > 65709
> >

Sorry, it should have been:

set rng = Columns("A").specialcells(xlConstants,xlNumbers)
for each ar in rng.Areas
set rng1 = ar.offset(ar.Rows.count).Resize(1,1)
rng1.Formula = "=Subtotal(9," & ar.Address(0,0) & ")"
Next

rather then xlBlanks. So you shouldn't need to do anyting but the above.

--
Regards,
Tom Ogilvy


"JimMay" <jmay@cox.net> wrote in message
news:cBVgg.40343$fG3.26390@dukeread09...
> Thanks Tom; I'm on a roll...
>
> Now that I can get (from immediate window):
> ? rng.Address
> $A$3,$A$5,$A$11,$A$16,$A$18
>
> How can I derive the variables --
> (Where A2 is the starting point)
> A3-A2 = 1 << number of rows to include in Subtotal)
> A5-A3+1 = 1
> A11-A5+1= 5
> A16-A11+1 = 4
> A18-A16+1 = 1
>
> Appreciate all your help..
> I'm getting there..
> Jim
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:#AGJCfEiGHA.1264@TK2MSFTNGP05.phx.gbl:
>
> > The blank cells in column A are already there:
> >
> > Dim rng as Range, ar as Range, rng1 as Range
> > set rng = Columns("A").specialcells(xlBlanks)
> > for each ar in rng.Areas
> > set rng1 = ar.offset(ar.Rows.count).Resize(1,1)
> > rng1.Formula = "=Subtotal(9," & ar.Address(0,0) & ")"
> > Next
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "JimMay" <jmay@cox.net> wrote in message
> > news:cILgg.40329$fG3.21866@dukeread09...
> >
> > > I have a couple hundred lines of Check Amts and Check Numbers (as
text)
> > > like below..
> > > I need to Loop from bottom to top and Sum the previous amts applicable
> > > to
> > > The same check number. Bottom line ColA should = 12660; 2 rows up
> > > should = 1,583.96, etc up to Row 2.
> > > Can someone get me started?
> > > TIA,
> > >
> > > ColA ColB
> > > 197.11 65704
> > > 65704
> > > 78.03 65705
> > > 65705
> > > 27.85 65706
> > > 1691.84 65706
> > > 122.98 65706
> > > 2275.13 65706
> > > 289.95 65706
> > > 65706
> > > 372.22 65707
> > > 57.96 65707
> > > 921.05 65707
> > > 232.73 65707
> > > 65707
> > > 12660 65709
> > > 65709
> > >
>

WOW !!
Holy Moly!! It works a treat.
I'm gonna study this one.
Thanks again,
Jim

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:#1iO7ZQiGHA.4512@TK2MSFTNGP02.phx.gbl:

> Sorry, it should have been:
>
> set rng = Columns("A").specialcells(xlConstants,xlNumbers)
> for each ar in rng.Areas
> set rng1 = ar.offset(ar.Rows.count).Resize(1,1)
> rng1.Formula = "=Subtotal(9," & ar.Address(0,0) & ")"
> Next
>
> rather then xlBlanks. So you shouldn't need to do anyting but the above.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JimMay" <jmay@cox.net> wrote in message
> news:cBVgg.40343$fG3.26390@dukeread09...
>
> > Thanks Tom; I'm on a roll...
> >
> > Now that I can get (from immediate window):
> > ? rng.Address
> > $A$3,$A$5,$A$11,$A$16,$A$18
> >
> > How can I derive the variables --
> > (Where A2 is the starting point)
> > A3-A2 = 1 << number of rows to include in Subtotal)
> > A5-A3+1 = 1
> > A11-A5+1= 5
> > A16-A11+1 = 4
> > A18-A16+1 = 1
> >
> > Appreciate all your help..
> > I'm getting there..
> > Jim
> >
> >
> > "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> > news:#AGJCfEiGHA.1264@TK2MSFTNGP05.phx.gbl:
> >
>
> > > The blank cells in column A are already there:
> > >
> > > Dim rng as Range, ar as Range, rng1 as Range
> > > set rng = Columns("A").specialcells(xlBlanks)
> > > for each ar in rng.Areas
> > > set rng1 = ar.offset(ar.Rows.count).Resize(1,1)
> > > rng1.Formula = "=Subtotal(9," & ar.Address(0,0) & ")"
> > > Next
> > >
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "JimMay" <jmay@cox.net> wrote in message
> > > news:cILgg.40329$fG3.21866@dukeread09...
> > >
>
> > > > I have a couple hundred lines of Check Amts and Check Numbers (as
>
> text)
>
> > > > like below..
> > > > I need to Loop from bottom to top and Sum the previous amts applicable
> > > > to
> > > > The same check number. Bottom line ColA should = 12660; 2 rows up
> > > > should = 1,583.96, etc up to Row 2.
> > > > Can someone get me started?
> > > > TIA,
> > > >
> > > > ColA ColB
> > > > 197.11 65704
> > > > 65704
> > > > 78.03 65705
> > > > 65705
> > > > 27.85 65706
> > > > 1691.84 65706
> > > > 122.98 65706
> > > > 2275.13 65706
> > > > 289.95 65706
> > > > 65706
> > > > 372.22 65707
> > > > 57.96 65707
> > > > 921.05 65707
> > > > 232.73 65707
> > > > 65707
> > > > 12660 65709
> > > > 65709
> > > >
>
> >