Insert formulas in the all rows of a column depending on another column that has data.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
168
Office Version
  1. 2013
Platform
  1. Windows
I have a macro that I got off the Internet that creates a sheet called “Summary-Sheet”.

The sheet is created by going through the workbook and for each Sheet, it inserts the sheet name in Sheet “Summary-Sheet” along with a couple pieces of data in Columns B and C.

Summary-Sheet has data in columns A, B and C and Row 1 contains headings.

In column E, I would like to have the formula =D2*E2. This formula would have to go down as far as the last row that has data in Column A.

I would like to have a formula in the last row +1 in Column E that would sum all the rows.

Appreciate any help offered.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You cannot have a formula in E2 that references itself.
 
Upvote 0
Fluff, that's not strictly true - though you need to allow iterative calculations in Options and I have no idea why you would since the output in this case the answer is 0 because that is what is first placed in E2. (no doubt you already no that, but I just had to check for myself).

Book1
DE
210
Sheet2
Cell Formulas
RangeFormula
E2E2=D2*E2


Kayslover needs to provide an example.

Regards
 
Upvote 0
Fluff, PJMorris,

Thank you both.

My bad, I made a mistake. In column E, I would like to have the formula =C2*D2. This formula would have to go down as far as the last row that has data in Column A.

I would like to have a formula in the last row +1 in Column E that would sum all the rows.
 
Upvote 0
Ok, how about
VBA Code:
Sub Kayslover()
   With Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=rc[-2]*rc[-1]"
      .Offset(.Rows.Count).Resize(1).FormulaR1C1 = "=sum(r2c:r[-1]c)"
   End With
End Sub
 
Upvote 0
Fluff,

Worked a Treat as expected.

Can I as for 1 more amendment.

The last Row which has the Sum formula, can the word TOTAL be inserted in the last row of Column A. Additionally, TOTAL and the value of the sum be in RED and Bolded
 
Upvote 0
How about
VBA Code:
Sub Kayslover()
   With Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
      .FormulaR1C1 = "=rc[-2]*rc[-1]"
      With .Offset(.Rows.Count).Resize(1)
         .FormulaR1C1 = "=sum(r2c:r[-1]c)"
         .Font.Color = vbRed
         .Font.Bold = True
         .Offset(, -4).Value = "TOTAL"
      End With
   End With
End Sub
 
Upvote 0
Fluff,

The word TOTAL is not in Red or Bolded, I therefore copied the following lines:-
VBA Code:
.Font.Color = vbRed
.Font.Bold = True
after the line
VBA Code:
.Offset(, -4).Value = "TOTAL"
but they do not Bold and RED the word TOTAL

How can TOTAl be in RED and Bolded?
 
Upvote 0
Does it matter if columns b:D are also red & bold?
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top