Macro? Inserting Rows and Adding Columns?

[boltwild]

I first want to say that this site is awesome. I've already used alot of the information given. Thanks for your time ya'll spend helping others.

With that said, here's my issue and what I want to do. I'm trying to insert two blank rows when a symbol (cusip) changes and then add up two joining columns and have a subtotal as the symbol changes. For example:

Column A | Column B | Column C | Column D | Column E
name | Desc| Symbol | shares | TMV
I'd like the macro to insert two blank rows and then total up column D & E and then it starts over when the symbol changes.

Like:
name | homer | xyz | 10 | 54.50
name | homer | xyz | 9 | 45.00
---blank --- 19 | 99.50
---blank ---
name | marge | 12ju | 50 | 75.00
name | marge | 12ju | 60 | 181.00
name | marge | 12ju | 5 | 15
---blank--- 115 | 271
---blank---

Is this possible? I've used one macro that seperates the symbols but doesn't add up those two columns. I'd like the bold figures to be in the same column tho. For some reason, I couldn't line it up.

Any help would be very much appreciated.
Thanks for taking your time.

bolt

 

[VoG]

As far as I can make out, this should work

Sub sbttlsFORMULA()
Dim LastRow As Long, i As Long, aArea As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
' When the value in cell "A" Changes, insert a row
For i = LastRow To 2 Step -1
    If Range("B" & i).Value <> Range("B" & i - 1).Value Then
        Rows(i).Resize(2).Insert
    End If
Next i

For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Cells(aArea.Row + aArea.Rows.Count, 4).Formula = "=SUM(" & Range(Cells(aArea.Row, 4), Cells(aArea.Row + aArea.Rows.Count - 1, 4)).Address & ")"
    Cells(aArea.Row + aArea.Rows.Count, 5).Formula = "=SUM(" & Range(Cells(aArea.Row, 5), Cells(aArea.Row + aArea.Rows.Count - 1, 5)).Address & ")"
Next aArea
End Sub

 

[boltwild]

As far as I can make out, this should work

Sub sbttlsFORMULA()
Dim LastRow As Long, i As Long, aArea As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
' When the value in cell "A" Changes, insert a row
For i = LastRow To 2 Step -1
    If Range("B" & i).Value <> Range("B" & i - 1).Value Then
        Rows(i).Resize(2).Insert
    End If
Next i

For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Cells(aArea.Row + aArea.Rows.Count, 4).Formula = "=SUM(" & Range(Cells(aArea.Row, 4), Cells(aArea.Row + aArea.Rows.Count - 1, 4)).Address & ")"
    Cells(aArea.Row + aArea.Rows.Count, 5).Formula = "=SUM(" & Range(Cells(aArea.Row, 5), Cells(aArea.Row + aArea.Rows.Count - 1, 5)).Address & ")"
Next aArea
End Sub

VoG,
You, my friend, are a life-timesaver hero! It works flawlessly. Now, audits aren't head scratchers! Thank you so much!!!

bolt

 

[Merri]

Hello to the board!

I, as well, want to thank you soooooooooo very much as this macro has been extremely useful in what I was trying to also accomplish. Plus it earned me a brownie point or two with the cute guy - ha ha ha.

The worksheet I am applying this to has the repeating data in column A, but a minor tweak changing the "B" to an "A" and 2 rows are inserted jiffy quick exactly where they need to be.

Now for the second part, I'm just doing a sum on the last column of numbers in my data and will change, say, the "4" to be whatever column number I need and delete the other line of macro data that creates a sum. Correct, yes?

But I do have a question?

What needs to be added to make the sum amount appear italicized or bold? Trying to decide which I want it to do. This small tweak may help differentiate that sum amount just enough for the staff who will be using this worksheet on a routine basis.

Thanks for the help!
Merri

 

[VoG]

Try like this

Sub sbttlsFORMULA()
Dim LastRow As Long, i As Long, aArea As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
' When the value in cell "A" Changes, insert a row
For i = LastRow To 2 Step -1
    If Range("B" & i).Value <> Range("B" & i - 1).Value Then
        Rows(i).Resize(2).Insert
    End If
Next i

For Each aArea In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants).Areas
    Cells(aArea.Row + aArea.Rows.Count, 4).Formula = "=SUM(" & Range(Cells(aArea.Row, 4), Cells(aArea.Row + aArea.Rows.Count - 1, 4)).Address & ")"
    Cells(aArea.Row + aArea.Rows.Count, 5).Formula = "=SUM(" & Range(Cells(aArea.Row, 5), Cells(aArea.Row + aArea.Rows.Count - 1, 5)).Address & ")"
    Rows(aArea.Row + aArea.Rows.Count).Font.Bold = True
Next aArea
End Sub

 

[Merri]

Thanks VoG, that worked perfectly!!

Do have another question though . . . . . The user of this worksheet has inquired if it will be possible to only sum part of the column of extended amounts. If this is possible, it will save them a step or two when they are getting all this data into another 'master' sheet.

Couldn't get my data example to paste correctly after multiple tries (rats! darn!), so will try to describe the data.

Each invoice will contain at least 1 row of line item data. Most have 4+. Not all have highlighted cells.
Column A : Invoice number
Column B: Line item description
Column C: Unit amount
Column D: Extended price of item

I am running a separate macro first -- although thinking about including it with the one that inserts the rows and calculates the sum -- which color codes certain descriptions in column B and then sorts each invoice's line item data to have those color-coded cells on top. This works great and gives the user a good visual of those items as they do need to be entered separately into the 'master' sheet.

The question is , , , can the macro sum only the amounts in column D based on the variable non-highlighted cells of column B??

Let me try to paste my example data one more time . . . and my apologies for the crudeness of this example . .
Inv ____ Descr. ________ Unit $ _____ Ext. Price
36 _____ Ia (color) _____ 25.00 ________ 25.00
36 _____ Ib (color) _____ 46.00 ________ 92.00
36 _____ B __________ 777.00 ______ 1,554.00
36 _____ C ___________ 10.00 _________ 20.00
36 _____ O ________ 3,930.00 _______ 3,930.00
36 _____ P __________ 297.00 ________ 594.00
36 _____ T ____________ 5.00 _________ 10.00
_________________________________6,225.00

Can the sum only inlcude the Ext. Price of the non-highlighted cells of Column B, which for this example would sum to 6,108.00??

Greatly appreciate your help with this one!!!

Merri

 

[VoG]

That is more difficult. See if you can adapt this http://www.ozgrid.com/VBA/Sum.htm

 

[Merri]

Hi VoG.

Sorry. I'm wayyyy lost on this one.

I may just have to leave the macro as is.

Maybe take out the sum part entirely, which will leave a place so the user can manually put in the sum of the cells they want.


Merri