Help with vba to sum cells and also subtract cells

Gregm66

Board Regular
Joined
Jan 23, 2016
Messages
170
Hi everyone,

I have looked at different answers to this question with no success as when i run the vba my workbooks freezes and then wont do anything.

I have a worksheet called "Club Ledger" that i need to sum a range of cells in column C5 to C105001 and return the result to Cell F5..

Then sum the range G5 to G105001 and return the value to G5

and lastly i need to subtract cell G5 from F5 and return the value to H5

thanks in advance for any help
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi Gregm66,

Try this:

Code:
Option Explicit
Sub Macro1()

    With Sheets("Club Ledger")
        .Range("F5").Formula = "=SUM(C5:C105001)"
        .Range("G5").Formula = "=SUM(G5:G105001)"
        .Range("H5").Formula = "=G5-F5"
    End With

End Sub

Regards,

Robert
 
Upvote 0
Hi Trebor76,

The code works fine as a macro, what i would prefer and i do appologise i should have said.
i would like the code to run everytime a new value is added to the next cell..
Ie: under the Worksheet_Change event or Worksheet_Update.. Preferably the later as i already run code in the Worksheet_Change event.

Regards
Greg
 
Upvote 0
ok i changed the Sub Macro1() to Private Sub Macro1() and it works fine,
although now i am confused it actually places code in to cells F5 , G5 and H5 is there a way to prevent it placing the code into the cells as i am worried that a user my accidently delete the code in those cells.
 
Upvote 0
Why would you want the same formula to be placed in the cells whenever a cell is changed as the answer will always be the same?

What do you mean "places the code" in those cells? The code will put formulas in cells F5:H5 - at least it did for me :confused:
 
Upvote 0
As soon as i put a value into cell C5, C6, C7 it will sum all those values and return the value to F5 as required.
and if i click on cell F5 it shows the following formula even after i deleted it..

Code:
=SUM(C5:C105001)

i am trying to prevent any formulas being shown in cells, if this makes it more understandable
 
Upvote 0
How is Row 105001 determined? Also the value for cell G5 will have to be from a formula starting form cell G6 or else there will be a circular reference.

if i click on cell F5 it shows the following formula even after i deleted it

No idea what you mean??
 
Last edited:
Upvote 0
Row 105001 is the amount i have set for entries.. i have code that auto inserts the date an entry is made although it does not refer to row 105001..
and cell G5 will only show the total.. as will F5 and H5..
its ok if it puts a formula in the cell i was just trying to avoid any formulas being in any cells thats all.. as the rest of my workbook uses vba
 
Upvote 0
For Col. G I take it the data starts at Row 6? You can't have =SUM(G5:G105001) in cell G5.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
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