Problem with a loop and column references

L

L. Howard

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.
--

Aha, I'll give that a go.

Actually the (3) I have been using only puts one blank row above the totals.

Also, how would I capture all those total cells in a With Statement so I can format them all at once?

Would want .Font Bold = true
.Interior.colorindex = 17
.Top border = xlMedium
.Bottom border = xlMedium

This is turning out much more complicated than I first thought.

Howard
 
C

Claus Busch

Hi Howard,

Am Thu, 4 Dec 2014 05:18:21 -0800 (PST) schrieb L. Howard:
Would want .Font Bold = true
.Interior.colorindex = 17
.Top border = xlMedium
.Bottom border = xlMedium

try it this way:

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&
Application.ScreenUpdating = False

For n = 28 To 89
If n Mod 5 <> 2 Then
lRow = Cells(Rows.Count, n).End(xlUp).Row
With Cells(lRow + 3, n)
.Value = WorksheetFunction.Sum(Columns(n))
.Font.Bold = True
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End If
Next 'n
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
C

Claus Busch

Hi again,

Am Thu, 4 Dec 2014 14:37:23 +0100 schrieb Claus Busch:

sorry forgot the interior.colorindex:

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&
Application.ScreenUpdating = False

For n = 28 To 89
If n Mod 5 <> 2 Then
lRow = Cells(Rows.Count, n).End(xlUp).Row
With Cells(lRow + 3, n)
.Value = WorksheetFunction.Sum(Columns(n))
.Font.Bold = True
.Interior.ColorIndex = 17
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End If
Next 'n
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
 
L

L. Howard

Hi again,

Am Thu, 4 Dec 2014 14:37:23 +0100 schrieb Claus Busch:

sorry forgot the interior.colorindex:

Sub SumMyCols()
' Totals each col in sets of grouped cols
Dim n&, lRow&
Application.ScreenUpdating = False

For n = 28 To 89
If n Mod 5 <> 2 Then
lRow = Cells(Rows.Count, n).End(xlUp).Row
With Cells(lRow + 3, n)
.Value = WorksheetFunction.Sum(Columns(n))
.Font.Bold = True
.Interior.ColorIndex = 17
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
End If
Next 'n
Application.ScreenUpdating = True
End Sub


That's a winner, indeed. I caught the color and also added alignment center

Look really good.

Thanks much.

Howard
 
G

GS

if the rows above row 8 are empty or have text you can use the whole
Aha, I'll give that a go.

Actually the (3) I have been using only puts one blank row above the
totals.

Also, how would I capture all those total cells in a With Statement
so I can format them all at once?

Would want .Font Bold = true
.Interior.colorindex = 17
.Top border = xlMedium
.Bottom border = xlMedium

This is turning out much more complicated than I first thought.

Howard

You can also preformat that cell with the formula as formatting copies
with the cell! I assume you know how that works (Ctrl+drag)!

--
Garry

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

L. Howard

You can also preformat that cell with the formula as formatting copies
with the cell! I assume you know how that works (Ctrl+drag)!

Hmmm, I'm wondering if would recognize that if I saw it.

Are you meaning something like cell.value = 100 .format.currency or something like that?

I'll take a google at it to see what I can come up with.

Howard
 
G

GS

Hmmm, I'm wondering if would recognize that if I saw it.

Are you meaning something like cell.value = 100 .format.currency or
something like that?

I'll take a google at it to see what I can come up with.

Howard

No! I mean my suggestion to add defined name 'LastCell' and use it as
stated (drag-copy). The formatting will tag along!

--
Garry

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

L. Howard

No! I mean my suggestion to add defined name 'LastCell' and use it as
stated (drag-copy). The formatting will tag along!
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.


You are referring to this post.

This puzzles me. Not understanding the full technique to make that happen.

Here is a link to a test sheet that pretty much does all the stuff.

I would be interested in the named range caper if you have time to demo it on this sheet.

https://www.dropbox.com/s/lriboaq8lxqzxeu/Twelve column group total.xlsm?dl=0

I'm happy with the existing until I get feedback from user.

And if you have more pressing issues I understand.

Thanks.
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top