Excel Macro Help Insert Rows, Sum Data

August 18, 2011 at 21:07:25
Specs: Windows 7
I have started recently to mess around with macros but I can't quite figure out how to do this one.

I have this list of songs with three columns (artist, title and track #) but what i want to do with it is to insert a row between each artist while dropping down the track #. But when the artist is the same i want to add these track numbers.

Any help would be appreciated
Thanks in advance!


See More: Excel Macro Help Insert Rows, Sum Data

Report •


#1
August 19, 2011 at 01:39:10
I don't understand what you are trying to do.

Please click on the following line and after reading the instructions found via that link, post a small sample of your data, before and after.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 19, 2011 at 07:19:27
For example this is what I would be starting with:

    A                           B                      C
1    Beenie Man 	        Jiggle 	               0
2    Breaking Benjamin          Rain	              11
3    Bruno Mars	                Bulletproof 	       0
4    Bruno Mars	                Grenade 	       5
5    Chiddy Bang	        Meet Mike 	       8

And this would be the end product

1    Beenie Man 	        Jiggle 	               0
2                                                      0
3    Breaking Benjamin          Rain	              11
4                                                     11
5    Bruno Mars	                Bulletproof 	       0
6    Bruno Mars	                Grenade 	       5
7                                                      5
8    Chiddy Bang	        Meet Mike 	       8
9                                                      8


Report •

#3
August 19, 2011 at 07:55:29
Obviously you didn't read (or at least you didn't follow) the instructions found via the link at the bottom of this post.

Please try again.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 19, 2011 at 08:21:02
For example this is what I would be starting with:

    A                           B                      C
1    Beenie Man 	        Jiggle 	               0
2    Breaking Benjamin          Rain	              11
3    Bruno Mars	                Bulletproof 	       0
4    Bruno Mars	                Grenade 	       5
5    Chiddy Bang	        Meet Mike 	       8

And this would be the end product

1    Beenie Man 	        Jiggle 	               0
2                                                      0
3    Breaking Benjamin          Rain	              11
4                                                     11
5    Bruno Mars	                Bulletproof 	       0
6    Bruno Mars	                Grenade 	       5
7                                                      5
8    Chiddy Bang	        Meet Mike 	       8
9                                                      8


Report •

#5
August 19, 2011 at 16:13:17
In your original post, you said:

"But when the artist is the same i want to add these track numbers."

In your example, it's kind of hard to see that since the only duplicate you have is Bruno Mars with track values of 0 and 5. Since 0+5 = 5 it makes your example look as if you simply copied the 5 from the cell above, just like all the other track numbers.

I only point that out so that the next time you post an example if what you are looking for, try to use an example that covers all of your requirements and is clear to the reader. Had I not gone back and re-read your original post, I would have missed the part about "adding" the track values for duplicate artists.

Something like this would have been a lot clearer:

For example this is what I would be starting with:
    A                           B                      C
1    Beenie Man 	        Jiggle 	               0
2    Breaking Benjamin          Rain	              11
3    Bruno Mars	                Bulletproof 	       4
4    Bruno Mars	                Grenade 	       5
5    Chiddy Bang	        Meet Mike 	       8

And this would be the end product

1    Beenie Man 	        Jiggle 	               0
2                                                      0
3    Breaking Benjamin          Rain	              11
4                                                     11
5    Bruno Mars	                Bulletproof 	       4
6    Bruno Mars	                Grenade 	       5
7                                                      9
8    Chiddy Bang	        Meet Mike 	       8
9                                                      8

OK, so back to your request...

I realize that you ask to drop down the track # single entries and add the track values for multiple entries, but it is much easier to write code that SUMs the track values for all artists by simply adjusting the range that gets summed.

In other words, when there is a single entry the formula will be e.g. =SUM(C3:C3) but if there are multiple entries the formula will be e.g. =SUM(C3:C5). It easier to count how many entries to SUM than to copy a value for single entries and use a formula for multiple entries. Formulas for all just makes the code more efficient.

Run this code and then click on the new values in Column C to see what I mean.

Option Explicit
Sub SplitRows()
Dim lastRw, rw, formRw As Integer
'Handle Error When rw = 0
On Error GoTo errHandler
'Determine Last Row With Data + 1
  lastRw = Cells(Rows.Count, 1).End(xlUp).Row + 1
'Loop Through Rows, Insert Row and Formula
     For rw = lastRw To 2 Step -1
       Cells(rw, 3).EntireRow.Insert
       formRw = rw
'Do-Loop To Count Rows With Duplicate Entries
             Do Until Cells(rw - 1, 1) <> Cells(rw - 2, 1)
               rw = rw - 1
             Loop
'Place Formula In New Row
        Cells(formRw, 3).Formula = _
               "=SUM(C" & rw - 1 & ":C" & formRw - 1 & ")"
     Next
errHandler:
'Handle Error When rw = 0
 If Err.Number <> 0 Then
    Cells(formRw, 3).Formula = _
               "=SUM(C" & rw - 1 & ":C" & formRw - 1 & ")"
 End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question