Problem with a loop and column references

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

Part way there.

Twelve groups of 4 columns with a blank column between each group and a two column group at the end.

Each column in each group will have the same number of rows and that number will change often.

Each group will have a different number of rows from the other groups but again always the same number of rows within the group.

The code below puts the sum formula properly at the end of each column and in each group (except the two column group which I will probably write a separate line bit code to take care of them).

The LastRow is giving me fits as I need it to refer to each group as the For iI loop does it looping.

Second major problem is getting the formula to refer to proper columns as it loops. As it is, it refers only to the first group.

The commented out LastRow works but only refers to the first group.

Thanks,
Howard

Sub SumMyCols()

Dim i As Long
Dim LastRow As Long, myCol As Long, iI As Long
Dim sumRng As Range

'("AB:AE, AG:AJ, AL:AO, AQ:AT, AV:AY, BA:BD, BF:BI, BK:BN, BP:BS, BU:BX, BZ:CC, CE:CH, CJ:CK")

'LastRow = Range("AB:AE").Find(What:="*", after:=[ab8], _
searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

' MsgBox LastRow


For iI = 28 To 86

LastRow = Range(Cells(9, iI)).Find(What:="*", after:=Range(Cells(8, iI)), _
searchorder:=xlByRows, _
searchdirection:=xlPrevious).Row

Cells(LastRow + 2, iI).Resize(1, 4) = Application.WorksheetFunction.Sum(Range("AB9:AB" & LastRow))
iI = iI + 4

Next 'iI

End Sub
 
Are the rows in each group contiguous? If so you just need to find the
last data row (sound familiar?) and exit the loop at the 1st empty
value.

OR

Use an array's 2nd dim to determine where empty cols are and where last
data row is for each col of UsedRange.

OR

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB1").Column To Range("CK1").Column
lRow = WorksheetFunction.CountA(Columns(n))
If lRow > 0 Then _
Cells(lRow + 1, n) = WorksheetFunction.Sum(Columns(n))
Next 'n
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Howard,

Am Wed, 3 Dec 2014 19:17:09 -0800 (PST) schrieb L. Howard:
Twelve groups of 4 columns with a blank column between each group and a two column group at the end.

Each column in each group will have the same number of rows and that number will change often.

Each group will have a different number of rows from the other groups but again always the same number of rows within the group.

The code below puts the sum formula properly at the end of each column and in each group (except the two column group which I will probably write a separate line bit code to take care of them).
Click to expand...

you also can set a rngBig and loop through all columns of this range:

Sub mySum()
Dim rngBig As Range, myCol As Range
Dim LRow As Long, ColNR As Long

With Sheets("Sheet1")
Set rngBig = Union(.Range("AB:AE"), .Range("AG:AJ"), .Range("AL:AO"),
_
.Range("AQ:AT"), .Range("AV:AY"), .Range("BA:BD"),
..Range("BF:BI"), _
.Range("BK:BN"), .Range("BP:BS"), .Range("BU:BX"),
..Range("BZ:CC"), _
.Range("CH:CE"), .Range("CJ:CK"))
For Each myCol In rngBig.Columns
LRow = .Cells(Rows.Count, myCol.Column).End(xlUp).Row
ColNR = myCol.Column
.Cells(LRow + 1, ColNR).Formula = "=Sum(" & Cells(1,
ColNR).Address & ":" & _
Cells(LRow, ColNR).Address & ")"
Next
End With
End Sub


Regards
Claus B.
 
Very nice as usual.

This works on my example with the small modification to write the totals below the data.

The data starts in row 9 w/ headers and formula-produced values from there on down.


Sub SumMyColsGarry()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB9").Column To Range("CK9").Column
lRow = WorksheetFunction.CountA(Columns(n))

If lRow > 0 Then _
Cells(Rows.Count, n).End(xlUp)(3) = WorksheetFunction.Sum(Columns(n))

Next 'n
End Sub



<the rows in each group contiguous? If so you just need to find the
last data row (sound familiar?) and exit the loop at the 1st empty
value.>

Yes to this question, contiguous or none at all from row nine on down.

If I read it correctly, that was what I was trying to do which I could make work with the first group, but was hung up on finding the 1st empty for each group as the loop continued.


<Use an array's 2nd dim to determine where empty cols are and where last
data row is for each col of UsedRange.>

This method puts me in my twilight zone of arrays were I seem to be stuck forever. I would like to see an example of how that would work on the column group I described here.

12 - 4 columns groups with one column space between each group, with a 2 column group at the end. Headers in row 9, data in contiguous rows, rows vary in number by group, but are the same within the group.

You have given me something that works, so this last part can surely be a 'time permitting' thing.

Thanks,
Howard
 
Hi again,

Am Thu, 4 Dec 2014 08:27:43 +0100 schrieb Claus Busch:
Sub mySum()
Click to expand...

or try:

Sub mySum2()
Dim i As Long, LRow As Long

With Sheets("Sheet1")
For i = 28 To 89
If i Mod 5 <> 2 Then
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(LRow + 1, i).Formula = "=SUM(" & Cells(1, i).Address & _
":" & Cells(LRow, i).Address & ")"
End If
Next
End With
End Sub


Regards
Claus B.
 
Sub SumMyColsGarry()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB9").Column To Range("CK9").Column
lRow = WorksheetFunction.CountA(Columns(n))

If lRow > 0 Then _
Cells(Rows.Count, n).End(xlUp)(3) =
WorksheetFunction.Sum(Columns(n))

Next 'n
End Sub
Click to expand...

If rows are contiguous then and you want to place the sum 3 rows below
then...

If lRow > 0 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

...works as well without the extra processing!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
you also can set a rngBig and loop through all columns of this range:
Sub mySum()
Dim rngBig As Range, myCol As Range
Dim LRow As Long, ColNR As Long

With Sheets("Sheet1")
Set rngBig = Union(.Range("AB:AE"), .Range("AG:AJ"), .Range("AL:AO"),
_
.Range("AQ:AT"), .Range("AV:AY"), .Range("BA:BD"),
.Range("BF:BI"), _
.Range("BK:BN"), .Range("BP:BS"), .Range("BU:BX"),
.Range("BZ:CC"), _
.Range("CH:CE"), .Range("CJ:CK"))
For Each myCol In rngBig.Columns
LRow = .Cells(Rows.Count, myCol.Column).End(xlUp).Row
ColNR = myCol.Column
.Cells(LRow + 1, ColNR).Formula = "=Sum(" & Cells(1,
ColNR).Address & ":" & _
Cells(LRow, ColNR).Address & ")"
Next
End With
End Sub


Regards
Claus B.
Click to expand...

Hi Claus,

That looks pretty heavy duty, I'll give it a try.

Thanks.
Howard
 
Oops! ..forgot the start row...

If lRow > 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
If rows are contiguous then and you want to place the sum 3 rows below
then...

If lRow > 0 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

..works as well without the extra processing!
Click to expand...

You'd get a chuckle looking at the examples I tried to get that done.

Thanks again.

Howard
 
Here's an option I'd be likely to go with instead of using VBA...

Select any cell in row 2, say A2.
Add a defined name with local scope as follows:
Name:="LastCell"
RefersTo:=A1

Select the 1st column to receive a total, say AE.
Enter the following formula:
=SUM(AE$9:LastCell)
Drag-copy the cell (or blocks) to anywhere you need a sum.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Oops again!
Here's an option I'd be likely to go with instead of using VBA...

Select any cell in row 2, say A2.
Add a defined name with local scope as follows:
Name:="LastCell"
RefersTo:="=A1"

Select the 1st column to receive a total, say AE.
Enter the following formula:
=SUM(AE$9:LastCell)
Drag-copy the cell (or blocks) to anywhere you need a sum.
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

Wow, lots of option coming faster than I can try them.

This below is what I am using at present. I'll check out the other options as soon as I can.

With the new option suggested (UNcommented in the code) does not produce totals.

Your original, which I modified, works fine. (it is commented OUT as is the code to delete the totals during testing etc.)

Does it matter if these ranges are AB8 & CK8 as opposed to AB1 & CK1?
Range("AB8").Column To Range("CK8").

Howard


Sub SumMyColsGarry()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AB8").Column To Range("CK8").Column
lRow = WorksheetFunction.CountA(Columns(n))

'/ No totals ???
If lRow > 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))

'/ Works okay
' If lRow > 0 Then _
Cells(Rows.Count, n).End(xlUp)(3) = WorksheetFunction.Sum(Columns(n))

'/ Used to delete totals for testing
' If lRow > 0 Then _
Cells(Rows.Count, n).End(xlUp)(1) = ""
Next 'n

End Sub
 
Hi howard,

Am Thu, 4 Dec 2014 08:35:47 +0100 schrieb Claus Busch:
Sub mySum2()
Click to expand...

if you want values instead of formulas try:

Sub mySum3()
Dim First As Range, Last As Range
Dim i As Long

With Sheets("Sheet1")
For i = 28 To 89
If i Mod 5 <> 2 Then
Set First = .Cells(9, i)
Set Last = .Cells(Rows.Count, i).End(xlUp)
Last.Offset(1, 0) = WorksheetFunction.Sum(.Range(First, Last))
End If
Next
End With
End Sub


Regards
Claus B.
 
Hi howard,

Am Thu, 4 Dec 2014 08:35:47 +0100 schrieb Claus Busch:


if you want values instead of formulas try:

Sub mySum3()
Dim First As Range, Last As Range
Dim i As Long

With Sheets("Sheet1")
For i = 28 To 89
If i Mod 5 <> 2 Then
Set First = .Cells(9, i)
Set Last = .Cells(Rows.Count, i).End(xlUp)
Last.Offset(1, 0) = WorksheetFunction.Sum(.Range(First, Last))
End If
Next
End With
End Sub


Regards
Claus B.
Click to expand...

Thanks, values will probably be best.

Yet to test all the options.

That is always fun and interesting to do so.

Thanks tons.

Howard
 
Hi Howard,

Am Wed, 3 Dec 2014 19:17:09 -0800 (PST) schrieb L. Howard:
Cells(LastRow + 2, iI).Resize(1, 4) = Application.WorksheetFunction.Sum(Range("AB9:AB" & LastRow))
Click to expand...

if you write a formula in a range the references will be modified. A
worksheetfunction does not do so. You can use formulas and change them
to values:

Sub mySum4()
Dim First As Range, Last As Range, rngS As Range
Dim i As Long

With Sheets("Sheet1")
For i = 28 To 88 Step 5
Set First = .Cells(9, i)
Set Last = .Cells(Rows.Count, i).End(xlUp)
Set rngS = Last.Offset(1, 0).Resize(1, IIf(i < 88, 4, 2))
With rngS
.Formula = "=SUM(" & Range(First, Last).Address(0, 0) & ")"
.Value = .Value
End With
Next
End With
End Sub


Regards
Claus B.
 
You're losing me! This works as tested per your description of
layout...

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&

For n = Range("AE1").Column To Range("CK1").Column
lRow = WorksheetFunction.CountA(Columns(n))
If lRow > 8 Then _
Cells(lRow + 3, n) = WorksheetFunction.Sum(Columns(n))
Next 'n
End Sub

...where, as you state, the data in each column is contiguous. I assumed
that also refers to rows 1 thru 8!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi again,

Am Thu, 4 Dec 2014 10:57:21 +0100 schrieb Claus Busch:
If lRow > 8 Then _
Cells(lRow + 8, n) = WorksheetFunction.Sum(Columns(n))
Click to expand...

sorry, my bad.
If there are values in the first 8 rows you are right


Regards
Claus B.
 
Hi again,
Am Thu, 4 Dec 2014 10:57:21 +0100 schrieb Claus Busch:


sorry, my bad.
If there are values in the first 8 rows you are right


Regards
Claus B.
Click to expand...

The totals are placed 3 rows below the last value to be summed!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Garry,

<..where, as you state, the data in each column is contiguous. I assumed
that also refers to rows 1 thru 8! >

This was in one of my replies, should have been more explicit and in my first post, an 'oops on my part. Sorry.

<The data starts in row 9 w/ headers and formula-produced values from there on down.>


The headers are in row 8 and data starts in row 9 and is contiguous.

Above that is blank, to the best of my knowledge.

Howard
 
Hi Howard,

Am Thu, 4 Dec 2014 03:32:05 -0800 (PST) schrieb L. Howard:
<The data starts in row 9 w/ headers and formula-produced values from there on down.>
Click to expand...

if the rows above row 8 are empty or have text you can use the whole
column because SUM ignores text.
To get the result 3 rows under the last row you have to change the 3 to
11 if cells in rows(1:7) are blank:
If lRow > 8 Then _
Cells(lRow + 11, n) = WorksheetFunction.Sum(Columns(n))


Regards
Claus B.
 
Back
Top