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

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
You cannot have a formula in E2 that references itself.
 

pjmorris

Well-known Member
Joined
Aug 2, 2012
Messages
1,972
Office Version
  1. 2016
Platform
  1. Windows
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
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Does it matter if columns b:D are also red & bold?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,265
Messages
5,600,603
Members
414,393
Latest member
Vignesh Mechz

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
Top