mhenk
Well-known Member
- Joined
- Jun 13, 2005
- Messages
- 591
Hey All -
I have a large list of data that I am trying to make more concise with a macro. Basically, right now, I have it so that it goes through the data, and where the value in a certain column changes, the Macro inserts 2 new rows, and adds some labels to them. I need to add some SUMIF formulas (preferably formulas, but summing in the macro is an acceptable second method).
Here's my code, before I get too far:
Now, between the "For b" and the "Next b," I want to add some SUMIF's, but the range length changes constantly, in that it is the number of records since the last change. I think that's what my CountRec variable is counting, but I'm not 100% sure it's working properly.
What I Ultimately Need:
1) Go through the data set
2) When the Value in Column D changes, insert 2 rows.
3) Copy down the labels for these two rows from the previous row
4) Add the labels in Column 5 (CAT, BUY-UP)
5) Add a SUMIF Calculation in the FIRST inserted row, 6th - 11th column
....a) This should SUM all the records since the last change in column D, (countrec entries, I think), IF the value in column 5 is equal to C
I have items 1-4 working, but the varying sumif is driving me nuts.
Any help would be appreciated. Thanks Much.
Let me know if I can clear anything up.
I have a large list of data that I am trying to make more concise with a macro. Basically, right now, I have it so that it goes through the data, and where the value in a certain column changes, the Macro inserts 2 new rows, and adds some labels to them. I need to add some SUMIF formulas (preferably formulas, but summing in the macro is an acceptable second method).
Here's my code, before I get too far:
Code:
Sub splitupcat_buyup()
'This macro inserts a row at each change in county, in order to easily SUM the
'non-CAT data entries in order to create concise exhibits
'Application.ScreenUpdating = False
Dim Current As String
Dim Previous As String
Dim countrec As Integer
countrec = 0
For my_rows = 3 To Range("A65536").End(xlUp).Row
countrec = countrec + 1
On Error GoTo Callout
Previous = Cells(my_rows - 1, 4).Value
Current = Cells(my_rows, 4).Value
If Previous <> Current Then
Rows(my_rows).Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
For A = 1 To 4
Cells(my_rows, A).Value = Cells(my_rows - 1, A).Value
Cells(my_rows + 1, A).Value = Cells(my_rows - 1, A).Value
Next A
Cells(my_rows, 5).Value = "CAT"
Cells(my_rows + 1, 5).Value = "BUYUP"
For b = 6 To 11
'Need SUMIFS here!
Next b
my_rows = my_rows + 2
countrec = 0
End If
Next my_rows
'Application.ScreenUpdating = True
Exit Sub
Callout:
h = MsgBox("FAILED")
'Application.ScreenUpdating = True
End Sub
Now, between the "For b" and the "Next b," I want to add some SUMIF's, but the range length changes constantly, in that it is the number of records since the last change. I think that's what my CountRec variable is counting, but I'm not 100% sure it's working properly.
What I Ultimately Need:
1) Go through the data set
2) When the Value in Column D changes, insert 2 rows.
3) Copy down the labels for these two rows from the previous row
4) Add the labels in Column 5 (CAT, BUY-UP)
5) Add a SUMIF Calculation in the FIRST inserted row, 6th - 11th column
....a) This should SUM all the records since the last change in column D, (countrec entries, I think), IF the value in column 5 is equal to C
I have items 1-4 working, but the varying sumif is driving me nuts.
Any help would be appreciated. Thanks Much.
Let me know if I can clear anything up.