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

 

[Alex Blakenburg]

@Joe4 - only if you change the if statement to this:

        If (Range("D31") = "") Or (Range("E31") = "") Then

 

[Joe4]

@Joe4 - only if you change the if statement to this:

        If (Range("D31") = "") Or (Range("E31") = "") Then

Alex,

If you remember your high school logic class, you will recall that:

If (Range("D31") = "") Or (Range("E31") = "")

(what you posted above)
is equivalent to:

If (Range("D31") <> "") And (Range("E31") <> "")

(what I used in my last code)

Think about it.
"If either one is blank"
is the same as:
"If both are NOT blank"

 

[kvsrinivasamurthy]

Ref Post#5
Does E5:E30 is formatted as text.

 

[Joe4]

Ref Post#5
Does E5:E30 is formatted as text.

If the SUM formula is working for them, then they are clearly not text values being entered.
If the SUM formula returns zero, then that would suggest that.

I think the bigger issue is that the code they were originally using was not being triggered on updates to column E.

 

[Alex Blakenburg]

which only will update D31 and E31 if they are currently blank.

vs

"If both are NOT blank"

 

[Joe4]

vs

A poor choice of words on my part. I should have said:

which only will update D31 and E31 if the either one is currently blank.

which more accurately describes what is going on.

However, I also did want to point out that the code revision you posted did not change anything - it does the exact same thing my code there does.
So, I think it was really my poor explanation that you probably had problems with, and not the code itself!

 

[Alex Blakenburg]

@ipbr21054
If you were going to use the code in Post #16 here is a revised version.
It contains a correction to the If statement so that it only adds the formulas if there is currently no formula in either D31 or E31 and turns off/on enable events to stop it calling itself.

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").HasFormula = False) Or (Range("E31").HasFormula = False) Then
            Application.EnableEvents = False
            Range("D31:E31").FormulaR1C1 = "=SUM(R[-26]C:R[-1]C)"
            Application.EnableEvents = True
        End If
    End If
    
End Sub

 

[Joe4]

@ipbr21054
If you were going to use the code in Post #16 here is a revised version.
It contains a correction to the If statement so that it only adds the formulas if there is currently no formula in either D31 or E31 and turns off/on enable events to stop it calling itself.

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").HasFormula = False) Or (Range("E31").HasFormula = False) Then
            Application.EnableEvents = False
            Range("D31:E31").FormulaR1C1 = "=SUM(R[-26]C:R[-1]C)"
            Application.EnableEvents = True
        End If
    End If
   
End Sub

Excellent updates, Alex!

I am in agrrement that if they do require the second block, this is the best code to use yet!