I found this VBA code but it doesn't work because it doesn't select the group and I cannot figure how to make it work.
Please Login or Register to view this content.
The code here should be group each set of Italics together.
Here is an example of what it should be grouping:
A B C D E F
Alaska ITEM1
Alaska ITEM2
Alaska ITEM3
COLD EAST ITEM1
COLD EAST ITEM2
COLD EAST ITEM3
Last edited by popfan; 12-16-2011 at 05:29 PM.
I believe you will need to have your macro insert blank rows between each group to get them to behave independently. Is that ok?
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
This macro would do that:
Please Login or Register to view this content.
This is not quite working the way you wanted it to. I am going to try to attach a spreadsheet to show you what I am working with.
http://mediafire.com/?a7mz6h5ah222a3i
Sorry about the size it has some macro I am using to format the file to the correct look. I just need the Italic parts grouped together. I can do the rest.
TOTAL should not be Italicized
Last edited by popfan; 12-14-2011 at 11:32 AM.
Would filtering your data work? Use DATA>FILTER and in column F, select Promo and Regular.
Not really, because that would cause all my formatting to end up being jacked up, i think. I need to the macro to select the row based on the Italics (mostly like easier then selected each set of promo and regular individually) and create at group at each set.
Might I suggest using Pivot Tables? Attached is a copy of a PT version of your table.
I would do this but, I have calculated fields that are based on totals and subtotal from the Sale Area. I removed them from the sheet for speed purposes and pivot tables are a big pain when have to add in calculations to each area.
popfan,
I used this on your example after removing all the formatting. See attached file.
Please Login or Register to view this content.
Macro in previous file crashed. Try this one.
Perfect. How do I add calculation into Column 12 with the formula = J(row here)/K(Row here) - 1 into the subtotal area?
Not sure what you mean by Row Here, but to include other columns into the subtotals, change the 2nd TotalList array. I.e. TotalList:=Array(7, 10, 11)
I figured out the summing in an array. But now, need to calculate the subtotal area with a the formula = J(cell from cell subtotal)/I(cell from cell subtotal) -1 or how to copy a value from the cell above into the subtotal row. Example - I need Sale Area to put into each Subtotal area (red font)
This is the normal out put
ALASKA 152 0 ALASKA - 0 PROMO 237.49 77.73
ALASKA 152 0 ALASKA - 0 REGULAR 279.19 241.74
ALASKA - 0 Total 516.68 319.47
ALASKA Total 516.68 319.47
This is what I am looking for
Sale Area Region SubRegion Region Name Promo Current Sales $ Previous Sales
ALASKA 152 0 ALASKA - 0 PROMO 237.49 77.73
ALASKA 152 0 ALASKA - 0 REGULAR 279.19 241.74
ALASKA ALASKA - 0 Total 516.68 319.47
ALASKA Total 516.68 319.47
Last edited by popfan; 12-15-2011 at 01:42 PM.
Try modifying these two lines...
Please Login or Register to view this content.
Originally Posted by dangelor
Try modifying these two lines...
Please Login or Register to view this content.
Not quite what I was referring to. See Attached sheet - take a look at the notes added. I would like to see if I can add a macro to generate those formulas.
I think I found my answer:
Please Login or Register to view this content.
Thanks all for your help!
Last edited by popfan; 12-16-2011 at 05:29 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks