Help with vba to sum cells and also subtract cells

[Gregm66]

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

 

[Gregm66]

Sorry Trebor76,
My internet dropped out...
The first cell in column G that has a value is Cell G5... and same for H it is also H5 that holds the first value

 

[Gregm66]

Column G starts at G5 that cell and H5 are the first 2 cells that hold a value

 

[Trebor76]

The first cell in column G that has a value is Cell G5... and same for H it is also H5 that holds the first value

So this will put the desired results from your original request in cells F4:H4 whenever there's a change in C5:C105001 or G5:G105001:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("C5:C105001,G5:G105001")) Is Nothing Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            Range("F4").Value = Evaluate("SUM(C5:C105001)")
            Range("G4").Value = Evaluate("SUM(G5:G105001)")
            Range("H4").Value = Evaluate("G4-F4")
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If

End Sub

The above event code will have to on the "Club Ledger" tab.

HTH

Robert

 

[Gregm66]

Thankyou a million Trebor76 problem solved that works perfectly your worth your weight in gold..

Thanks again

Regards
Greg

 

[Trebor76]

Thanks for letting us know and you're welcome