Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting variable ranges and auto-summing variable ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting variable ranges and auto-summing variable ranges
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:D this section Range("B" & BotRow + 1).Copy Range("C" & BotRow + 1 & ":D" & BotRow + 1) '' Set Bold Font Range("A" & BotRow + 1 & ":D" & BotRow + 1).Font.Bold = True '' now select nexts account Range("A" & BotRow + 3).Select Loop Until IsEmpty(ActiveCell) End Sub "Father Guido" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting variable ranges and auto-summing variable ranges
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" wrote: ~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:D this section ~ Range("B" & BotRow + 1).Copy Range("C" & BotRow + 1 & ":D" & BotRow + 1) ~ '' Set Bold Font ~ Range("A" & BotRow + 1 & ":D" & BotRow + 1).Font.Bold = True ~ '' now select nexts account ~ Range("A" & BotRow + 3).Select ~ ~Loop Until IsEmpty(ActiveCell) ~ ~End Sub ~ ~ ~ ~ ~ ~"Father Guido" wrote in message .. . ~ 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 ~ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable ranges | Excel Discussion (Misc queries) | |||
Help with Variable Ranges! | Excel Discussion (Misc queries) | |||
Variable Ranges | Excel Worksheet Functions | |||
Variable ranges | Excel Worksheet Functions | |||
Sum Variable Ranges | Excel Worksheet Functions |