Autosum Formila IN CELL ISSUES

[ipbr21054]

I select cell D31
I then type =SUM(D5:D30) & hit enter.
Cell D31 shows £0.00 but address / forula bar shows 0 as opposed =SUM(D5:D30)
My values are not adding up in cells D5 to D30 because of this.
The cells are formatted as Currency

Please advise how it can stay there & add up the values

 

[Joe4]

I think you are trying to do two entirely different things in your code right now, and are mixing things up.
It looks like this code was originally created to run against columns C and D, and to capitalize any entry made into these columns.
However, if you are entering numbers in column D, that really does not make sense.

Are you trying to capitalize entries into column C?
And then columns D and E will always be numeric entries?

If the sums will ALWAYS be in cells D31 and E31 (which seems to be the case, as you have hard-coded this range in your code), why have that in the code at all, instead of just having the dynamic sum formulas in cells D31 and E31 from the beginning?
Then they would not need to be a part of your VBA code at all.

 

[ipbr21054]

Ok so that explains thing.Capitalize columns B & C
£ values in column D & Numbers in colmn E

So that makes me use this correct ?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim Cell As Range
    
    Set rng = Intersect(Target, Range("B3:C" & Rows.Count))
    
'   Exit if nothing entered into out target range
    If rng Is Nothing Then Exit Sub
    
'   Loop through all cells in our target range
    Application.EnableEvents = False
    For Each Cell In rng
        Cell = UCase(Cell)
    Next Cell
    Application.EnableEvents = True
    Range("D31:E31").FormulaR1C1 = "=SUM(R[-26]C:R[-1]C)"
End Sub

Placing a in columns B & C now both change to A
Column D now adds the values from D5 to D30 with the total in cell D31
Column E now adds the value from E5 to E30 with the total in cell E31

 

[Joe4]

I think this would be a more accurate code for what you need:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim Cell As Range
    
'CODE BLOCK 1: Watch columns B and C for updates
    Set rng = Intersect(Target, Range("B3:C" & Rows.Count))
    
'   If an update is made to the range
    If Not rng Is Nothing Then
    
'       Loop through all cells in our tar7get range
        Application.EnableEvents = False
        For Each Cell In rng
            Cell = UCase(Cell)
        Next Cell
        Application.EnableEvents = True
    End If
    
'CODE BLOCK 2: Watch columns D and E for updates
    If Not Intersect(Target, Range("D5:E30")) Is Nothing Then
        Range("D31:E31").FormulaR1C1 = "=SUM(R[-26]C:R[-1]C)"
    End If
    
End Sub

Through I question if code block 2 is really needed at all. If you simply enter these formulas in D31 and E31 from the beginning, you wouldn't need this code at all.
It would re-enter the formula every time and update is made to the range D5:E30, but you really only need it to happen the first time.
But it doesn't hurt anything to re-enter it.

 

[Alex Blakenburg]

You might want to turn off/on events for the Sum section as well, since the code is triggering the change event.

 

[ipbr21054]

Ive used this.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim Cell As Range
    
'CODE BLOCK 1: Watch columns B and C for updates
    Set rng = Intersect(Target, Range("B3:C" & Rows.Count))
    
'   If an update is made to the range
    If Not rng Is Nothing Then
    
'       Loop through all cells in our tar7get range
        Application.EnableEvents = False
        For Each Cell In rng
            Cell = UCase(Cell)
        Next Cell
        Application.EnableEvents = True
    End If
    
End Sub

Cell D31 has =SUM(D5:D30) in

Cell E31 has =SUM(E5:E30)

 

[Joe4]

You might want to turn off/on events for the Sum section as well, since the code is triggering the change event.

I thought about it, and you could do that, but since neither cell D31 or E31 is in either of the watched ranges, it would call itself, and then end there.
You would definitely want to do if the values being updated were in either of the watched ranges.

I actually prefer this, that wouldn't update the formulas in cells D31 and E31 after the initial population:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim Cell As Range
    
'CODE BLOCK 1: Watch columns B and C for updates
    Set rng = Intersect(Target, Range("B3:C" & Rows.Count))
    
'   If an update is made to the range
    If Not rng Is Nothing Then
    
'       Loop through all cells in our tar7get range
        Application.EnableEvents = False
        For Each Cell In rng
            Cell = UCase(Cell)
        Next Cell
        Application.EnableEvents = True
    End If
    
'CODE BLOCK 2: Watch columns D and E for updates
    If Not Intersect(Target, Range("D5:E30")) Is Nothing Then
        If (Range("D31") <> "") And (Range("E31") <> "") Then
            Range("D31:E31").FormulaR1C1 = "=SUM(R[-26]C:R[-1]C)"
        End If
    End If
    
End Sub

 

[Joe4]

Ive used this.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim Cell As Range
   
'CODE BLOCK 1: Watch columns B and C for updates
    Set rng = Intersect(Target, Range("B3:C" & Rows.Count))
   
'   If an update is made to the range
    If Not rng Is Nothing Then
   
'       Loop through all cells in our tar7get range
        Application.EnableEvents = False
        For Each Cell In rng
            Cell = UCase(Cell)
        Next Cell
        Application.EnableEvents = True
    End If
   
End Sub

Cell D31 has =SUM(D5:D30) in

Cell E31 has =SUM(E5:E30)

Excellent!

I think that is the best way to go!
No reason to have VBA create something that can be done easily with Excel functions/formulas!

 

[ipbr21054]

I went the other route as something kept removing the =Sum code.
Maybe it was the b3@C change that now allows it

 

[Joe4]

I went the other route as something kept removing the =Sum code.
Maybe it was the b3@C change that now allows it

OK, if that continues to be a potential issue, you can use the code I posted in post 16, which only will update D31 and E31 if they are currently blank.

 

[Joe4]

Also note, just for education sake, this was the issue in trying to extend out your initial code:

    Set rng = Intersect(Target, Range("C3:D" & Rows.Count))
    
'   Exit if nothing entered into out target range
    If rng Is Nothing Then Exit Sub

If an update was happening outside of columns C or D, it was exiting the Sub without doing anything.
So, any updates to columns B and E would not do anything.

When we have multiple blocks of code checking multiple things, we do NOT want code exit the sub if the first condition is not met, or else it will NEVER get to the second condition.
So instead of having code checking to see if a change is happening within a certain range and exiting if it is not, we reverse the logic and tell it to only do those changes if it IS in that specific watched range. The "Exit Sub" strategy really only works if you are doing a single check.