Counting variable ranges and auto-summing variable ranges

[Father Guido]

It's been a while since I've used VBA, I'm hoping someone can
help me.

I have a spreadsheet of data for several accounts, I divide the
data into sections based on the account number in Column A. I
then insert two blank rows between each section. Now I need to
do two things. (1) In column A of the first blank row below each
data section, I need to calculate the number of entries in that
section, (2) using offset, moving to the right I need to do an
autosum on 3 columns.

Just recording the autosum function locks it into the first range of
cells autosum picks, so that won't work on any sections other than the
first -- I have ~500 sections and 8000 rows of data to analyse.

If I have to enter the autosum manually for 500 rows and 3 columns
(1500 entries) I'll die. Not to mention it'll take forever.

If you can help I'd really appreciate it.

Thanks,

Norm

 

[Rick Hansen]

Good Morning Norm,
Here is bit of code I believe will get the job done for you. Place the
activecell in the first line of first account in column A. This macro will
count the number of enters for each account, and auto sum columns B,C, & D.
Then proceed to the next accout, then repeat the process.
enjoy,
HTH , Rick Fbks, AK


Option Explicit

Sub AcctFormatStuff()
Dim LookRng As Range
Dim TopRow As Long, BotRow As Long

Do
Set LookRng = ActiveCell.CurrentRegion
TopRow = LookRng.Row
BotRow = LookRng.Rows.Count + TopRow - 1
'' count no# enter's this section
Range("A" & BotRow + 1).Formula = "=Count(A" & TopRow & ":A" & BotRow &
")"
'' sum col B this section
Range("B" & BotRow + 1).Formula = "=Sum(B" & TopRow & ":B" & BotRow & ")"
'' Copy sum formula from col B to C this section
Range("B" & BotRow + 1).Copy Range("C" & BotRow + 1 & "" & BotRow + 1)
'' Set Bold Font
Range("A" & BotRow + 1 & "" & BotRow + 1).Font.Bold = True
'' now select nexts account
Range("A" & BotRow + 3).Select

Loop Until IsEmpty(ActiveCell)

End Sub

 

[Father Guido]

Wow, great stuff -- works perfectly too! I never even thought of
attacking the problem from the top down, I was trying to go to the row
under the data and use the auto-sum.

Thanks again!!!

Norm


On Tue, 28 Mar 2006 00:02:10 -0900, "Rick Hansen"

~Good Morning Norm,
~ Here is bit of code I believe will get the job done for you.
Place the
~activecell in the first line of first account in column A. This macro
will
~count the number of enters for each account, and auto sum columns
B,C, & D.
~Then proceed to the next accout, then repeat the process.
~ enjoy,
~ HTH , Rick Fbks, AK
~
~
~Option Explicit
~
~Sub AcctFormatStuff()
~Dim LookRng As Range
~Dim TopRow As Long, BotRow As Long
~
~Do
~ Set LookRng = ActiveCell.CurrentRegion
~ TopRow = LookRng.Row
~ BotRow = LookRng.Rows.Count + TopRow - 1
~ '' count no# enter's this section
~ Range("A" & BotRow + 1).Formula = "=Count(A" & TopRow & ":A" &
BotRow &
~")"
~ '' sum col B this section
~ Range("B" & BotRow + 1).Formula = "=Sum(B" & TopRow & ":B" & BotRow
& ")"
~ '' Copy sum formula from col B to C this section
~ Range("B" & BotRow + 1).Copy Range("C" & BotRow + 1 & "" & BotRow
+ 1)
~ '' Set Bold Font
~ Range("A" & BotRow + 1 & "" & BotRow + 1).Font.Bold = True
~ '' now select nexts account
~ Range("A" & BotRow + 3).Select
~
~Loop Until IsEmpty(ActiveCell)
~
~End Sub
~
~
~
~
~
~~> It's been a while since I've used VBA, I'm hoping someone can
~> help me.
~>
~> I have a spreadsheet of data for several accounts, I divide the
~> data into sections based on the account number in Column A. I
~> then insert two blank rows between each section. Now I need to
~> do two things. (1) In column A of the first blank row below each
~> data section, I need to calculate the number of entries in that
~> section, (2) using offset, moving to the right I need to do an
~> autosum on 3 columns.
~>
~> Just recording the autosum function locks it into the first range
of
~> cells autosum picks, so that won't work on any sections other than
the
~> first -- I have ~500 sections and 8000 rows of data to analyse.
~>
~> If I have to enter the autosum manually for 500 rows and 3 columns
~> (1500 entries) I'll die. Not to mention it'll take forever.
~>
~> If you can help I'd really appreciate it.
~>
~> Thanks,
~>
~> Norm
~