Mid column sum formulas with varied ranges to sum

[L. Howard]

Tough to describe, please take a look at the test sheet.
Similar to recent post but the sheet format is much different.

If you will click the "Class row" row button then enter a 1 in the box, youwill see a row inserted at the change of each class value. I have a code line that simulates adding the formulas in the data field, it puts "XX" in the first group of four columns.

Frankly I am not sure how I made that work but it is merely a start.

Each XX will need to be a formula (or the result of a formula) to add the cells above it to up to the next class break.

And to go on across the sheet to the end of the data field.

Skip a column every four columns and four more formulas. I cannot even come up with the formulas nor how to traverse the newly inserted rows with theformulas.

The formula cells need to be formatted, Bold, Top & Bottom Border, and interior colorindex 17.

The "Reset C" button puts the data back as was before rows were inserted.

https://www.dropbox.com/s/1aiz6hstvgof8zn/Tracker Sheet.xlsm?dl=0

Thanks,
Howard

 

[Claus Busch]

Hi Howard,

Am Fri, 5 Dec 2014 17:12:46 -0800 (PST) schrieb L. Howard:

If you will click the "Class row" row button then enter a 1 in the box, you will see a row inserted at the change of each class value. I have a code line that simulates adding the formulas in the data field, it puts "XX" in the first group of four columns.

Each XX will need to be a formula (or the result of a formula) to add the cells above it to up to the next class break.

please explain a little more. I understand to insert a row on class end
to sum the class items. But what should happen if you insert more than
one row? Then you make a sum out of the class items and the sum in the
row above. The second sum is twice the first sum. The third sum is twice
the second sum and so on. What is the reason behind?


Regards
Claus B.

 

[L. Howard]

please explain a little more. I understand to insert a row on class end

to sum the class items. But what should happen if you insert more than
one row? Then you make a sum out of the class items and the sum in the
row above. The second sum is twice the first sum. The third sum is twice
the second sum and so on. What is the reason behind?


Regards
Claus B.

Hi Claus, thanks for taking a look

I changed the link to show an example of how the finished product should look.

I only did two lines of formulas, but all the newly inserted rows would look like the two blue filled lines. Row 25 and row 33 would look the same as rows 14 and 17.

No total in column B or C. (column C is for test purposes only and does not exist on real sheet)

Later the data will change and the that need totals will occur in different rows.

I presume when the data is changed the formatting will revert to "None", then in the new data set the code will inserted new rows and totals and format them.

Howard

 

[L. Howard]

<Later the data will change and the that need totals will occur in different rows.>

Should say:

Later the data will change and then the totals will occur in different rows.

 

[L. Howard]

Another edit.

There would also be a total row at the bottom of the data, in row 37, it would total the Class 44 data in this example.

Howard

 

[Claus Busch]

Hi again,

Am Sat, 6 Dec 2014 03:35:07 -0800 (PST) schrieb L. Howard:

There would also be a total row at the bottom of the data, in row 37, it would total the Class 44 data in this example.

I have two additional questions:

1. Don't you need in $ column the sumproduct of units and price?

2. Is each 5th column empty? If so, it is easier to write formula and
format through AB:CK and delete it in each 5th column instead of looping
through the cells


Regards
Claus B.

 

[L. Howard]

I have two additional questions:

1. Don't you need in $ column the sumproduct of units and price?

2. Is each 5th column empty? If so, it is easier to write formula and
format through AB:CK and delete it in each 5th column instead of looping
through the cells


Regards
Claus B.

1. Don't you need in $ column the sumproduct of units and price?

Hmm, not sure. I don't know what to say on that.
What would be in the $ column if it were so?



2. Is each 5th column empty?

Yes this is true. That is how I entered the sum formulas in the two example rows. I pulled the first formula across, and then formatted all the cells while they were selected and deleted the unused 5th column cells. A code version of that makes good sense.

I did neglect to format the $ columns cell to currency.

Howard

 

[L. Howard]

...deleted the unused 5th column cells.

Actually just deleted the formatting, sorry.

H

 

[Claus Busch]

Hi Howard,

Am Sat, 6 Dec 2014 05:26:30 -0800 (PST) schrieb L. Howard:

...deleted the unused 5th column cells.

Actually just deleted the formatting, sorry.

have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Tracker Sheet2"

Run macro "InsertRows". That it works correctly I changed the formulas
into values.


Regards
Claus B.

 

[L. Howard]

Hi Howard,

Am Sat, 6 Dec 2014 05:26:30 -0800 (PST) schrieb L. Howard:


have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Tracker Sheet2"

Run macro "InsertRows". That it works correctly I changed the formulas
into values.

Well that is quite remarkable!!

The sumproduct make sense to me now...DUH on me!

I would think both SumProduct and Values would be the proper method to use. That is unconfirmed to me at present.

I will save a workbook with the formulas and if you would change the linked version to values, that should cover all the bases.

Thanks tons, and I will be sure to have your name on the code.

Howard

P.S.
I guess the only other thing I would ask is, I have this bale of straw, would you please write some code that will turn it into gold.

 

[Claus Busch]

Hi Howard,

Am Sat, 6 Dec 2014 09:04:44 -0800 (PST) schrieb L. Howard:

I would think both SumProduct and Values would be the proper method to use. That is unconfirmed to me at present.

if the user inserts rows with values you better work with formula. The
formula modifies automatically.
Do you have the newest version of the workbook? I had a little error in
the first one I uploaded.


Regards
Claus B.

 

[L. Howard]

Hi Howard,

Am Sat, 6 Dec 2014 09:04:44 -0800 (PST) schrieb L. Howard:


if the user inserts rows with values you better work with formula. The
formula modifies automatically.
Do you have the newest version of the workbook? I had a little error in
the first one I uploaded.

I just now downloaded a version and I noticed it ran quite a bit faster than a previous one.

I assume I have the latest.

Also, to change to values this seems to do it.

With .Cells(i, n)
.Formula = "=SUM(" & myRng1 & ")": .Value = .Value

And the same for the Sumproduct line.

So going from formulas to values or back looks like something I can do if needed.

Howard

 

[Claus Busch]

Hi Howard,

Am Sat, 6 Dec 2014 09:54:12 -0800 (PST) schrieb L. Howard:

.Formula = "=SUM(" & myRng1 & ")": .Value = .Value

I would prefer formulas. But if the user want values try in in one step:

Sub InsertRows()
Dim LRow As Long, Start As Long, i As Long
Dim wsh As Worksheet
Dim n As Long, lRowsInsert As Long, CountUniques As Long
Dim myRng1 As String, myrng2 As String

Const myRow As Long = 10
Const myCol As Long = 3

lRowsInsert = Application.InputBox("Enter number of rows to insert", _
"Insert rows", Type:=1)

If lRowsInsert = 0 Or lRowsInsert = False Then Exit Sub

If lRowsInsert < 1 Then
MsgBox "Error - please enter a value equal to or greater than 1"
Exit Sub
End If

MsgBox "This code will insert " & lRowsInsert & " row/s" & vbCr & _
"wherever CLASS values change in" & vbCr & "column " _
& myCol & " starting from row number " & myRow

Application.ScreenUpdating = False

Set wsh = Sheets("5. Tracker")
With wsh
LRow = .Cells(Rows.Count, myCol).End(xlUp).Row
CountUniques = Evaluate("=Countif(" & .Range(.Cells(myRow, myCol),
..Cells(LRow, myCol)).Address & _
"," & .Range(.Cells(myRow, myCol), .Cells(LRow, myCol)).Address &
")")

For i = LRow To myRow + 1 Step -1
If .Cells(i - 1, myCol) <> .Cells(i, myCol) And _
Len(.Cells(i - 1, myCol)) > 0 Then
Rows(i).Resize(rowsize:=lRowsInsert).Insert
i = i - lRowsInsert
End If
Next

Start = myRow
LRow = .Cells(Rows.Count, myCol).End(xlUp).Row
For i = Start To LRow + lRowsInsert
If Len(.Cells(i, 28)) = 0 And Not .Cells(i - 1, 28).HasFormula
Then
For n = 28 To 89
If n Mod 5 = 3 Or n Mod 5 = 0 Then
myRng1 = .Range(.Cells(Start, n), .Cells(i - 1,
n)).Address
With .Cells(i, n)
.Formula = "=SUM(" & myRng1 & ")"
.Interior.ColorIndex = 17
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
End With
ElseIf n Mod 5 = 4 Or n Mod 5 = 1 Then
myRng1 = .Range(.Cells(Start, n), .Cells(i - 1,
n)).Address
myrng2 = .Range(.Cells(Start, n - 1), .Cells(i - 1, n -
1)).Address
With .Cells(i, n)
.Formula = "=SUMPRODUCT(" & myRng1 & "," & myrng2 &
")"
.Interior.ColorIndex = 17
.Borders(xlEdgeTop).Weight = xlMedium
.Borders(xlEdgeBottom).Weight = xlMedium
.NumberFormat = "[$$-409]#,##0.00"
End With
End If
Next
Start = i + lRowsInsert
i = i + lRowsInsert
End If
Next
With .Range(.Cells(myRow, "AB"), .Cells(LRow,
"CK")).SpecialCells(xlCellTypeFormulas)
.Value = .Value
End With
End With

Application.ScreenUpdating = True
End Sub


Regards
Claus B.

 

[L. Howard]

Great. I'll keep in on hand and thanks again.

Howard

 

[Claus Busch]

Hi Howard,

Am Sat, 6 Dec 2014 10:47:21 -0800 (PST) schrieb L. Howard:

Great. I'll keep in on hand and thanks again.

I made some changes to make the code faster. Please look again in
OneDrive.


Regards
Claus B.

 

[L. Howard]

Hi Claus,

Indeed, it is faster on the 800+ rows I tested, about half the time of the others.

Can there be a FORMULA version of the faster code?

Howard

 

[Claus Busch]

Hi Howard,

Am Sat, 6 Dec 2014 11:34:35 -0800 (PST) schrieb L. Howard:

Can there be a FORMULA version of the faster code?

I have comments into the code. Comment out the 3 rows that change the
formulas to values.
If the user needs values he can change it back.


Regards
Claus B.

 

[L. Howard]

Hi Howard,

Am Sat, 6 Dec 2014 11:34:35 -0800 (PST) schrieb L. Howard:



I have comments into the code. Comment out the 3 rows that change the
formulas to values.
If the user needs values he can change it back.

Woops, sorry. Noticed the comments but did not read them. My bad and thanks again.

Howard