try this Sub enterTotals() lastcol = 20 For i = 4 To lastcol For Each rngArea In Columns(i).SpecialCells(xlCellTypeConstants, xlNumbers).Areas With rngArea.Offset(rngArea.Rows.Count).Resize(1, 1) .FormulaR1C1 = "=SUM(" & rngArea.Address(1, 1, xlR1C1) & ")" .Interior.ColorIndex = 6 End With Next rngArea Next End Sub
On Tue, Jul 19, 2011 at 6:16 AM, Natron <protoc...@gmail.com> wrote: > I have a spreadsheet I need to have multiple items summed at the first > blank row available...the following macro works great for Column 4 as > shown below, but I need to have it run through multiple columns up to > 20...I tried using an array using a for next loop on the array, but > couldn't get it figured out. I commented out some of what I was > attemptign. Any suggestions or a push in the right direction would be > appreciated! > > Sub enterTotals() > 'myArr = Array("4", "5", "7") > 'For i = LBound(myArr) To UBound(myArr) > For Each rngArea In Columns(4).SpecialCells(xlCellTypeConstants, > xlNumbers).Areas > With rngArea.Offset(rngArea.Rows.Count).Resize(1, 1) > .FormulaR1C1 = "=SUM(" & rngArea.Address(1, 1, xlR1C1) & > ")" > .Interior.ColorIndex = 6 > End With > Next rngArea > 'Next i > End Sub > > As a bonus I need to have some simple percentages figured on the same > blank total row the sumations are on I'd figure I would build that > into the loop as well unless there is a faster method. > > Thanks you are all great! > > -- > > ---------------------------------------------------------------------------------- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- *Regards* * * *Ashish Koul* *akoul*.*blogspot*.com <http://akoul.blogspot.com/> http://akoul.posterous.com/ *akoul*.wordpress.com <http://akoul.wordpress.com/> My Linkedin Profile <http://in.linkedin.com/pub/ashish-koul/10/400/830> P Before printing, think about the environment. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel