VB Script to insert row and calculate a range

R

rlee1999

Sorry, I am new to this. Please forgive my ignorance.

I recorded a macro that sorts items first by class (ie.108 or 217 located in
column F) and then by dollar amount (located in column S). There may be 10
items in a class.

I need the script to locate the last occurance of the class number (Column
F) on the sheet and then insert a row below.

The script needs to sum the dollar amounts in the new row in Column S.

I would appreciate any help I can get.

~Robert
 
B

Bob Phillips

Dim LastRow As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Cells(LastRow + 1, "S").FormulaR1C1 = "=SUM(R1C19:R[-1]C19)"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
R

rlee1999

Thank you for your quick reply. I inserted this into my macro after the sort
and it did not act as expected. I have 22 different classes, I need a row
insert after each class and to sum the total dollar amount for each class in
the new row.

As is Needs to be
A B...F...............AI A B...F...............AI
125 216.70 125 216.70
125 760.93 125 760.93
125 119.36 125 119.36
124 361.33
1189.06
124 105.80 124
361.33
124
105.80

467.13


In my original post I stated as an example, that the dollar amounts to be
summed were in Column S, which may have been an error on my part. The Dollar
amounts are actually in Column AI.

I hope you can help.

Thank you,

~Robert

Bob Phillips said:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Cells(LastRow + 1, "S").FormulaR1C1 = "=SUM(R1C19:R[-1]C19)"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

rlee1999 said:
Sorry, I am new to this. Please forgive my ignorance.

I recorded a macro that sorts items first by class (ie.108 or 217 located in
column F) and then by dollar amount (located in column S). There may be 10
items in a class.

I need the script to locate the last occurance of the class number (Column
F) on the sheet and then insert a row below.

The script needs to sum the dollar amounts in the new row in Column S.

I would appreciate any help I can get.

~Robert
 
B

Bob Phillips

I presume that this has been superseded by your later post?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

rlee1999 said:
Thank you for your quick reply. I inserted this into my macro after the sort
and it did not act as expected. I have 22 different classes, I need a row
insert after each class and to sum the total dollar amount for each class in
the new row.

As is Needs to be
A B...F...............AI A B...F...............AI
125 216.70 125 216.70
125 760.93 125 760.93
125 119.36 125 119.36
124 361.33
1189.06
124 105.80 124
361.33
124
105.80

467.13


In my original post I stated as an example, that the dollar amounts to be
summed were in Column S, which may have been an error on my part. The Dollar
amounts are actually in Column AI.

I hope you can help.

Thank you,

~Robert

Bob Phillips said:
Dim LastRow As Long
LastRow = Cells(Rows.Count, "F").End(xlUp).Row
Cells(LastRow + 1, "S").FormulaR1C1 = "=SUM(R1C19:R[-1]C19)"


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

rlee1999 said:
Sorry, I am new to this. Please forgive my ignorance.

I recorded a macro that sorts items first by class (ie.108 or 217
located
in
column F) and then by dollar amount (located in column S). There may be 10
items in a class.

I need the script to locate the last occurance of the class number (Column
F) on the sheet and then insert a row below.

The script needs to sum the dollar amounts in the new row in Column S.

I would appreciate any help I can get.

~Robert
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top