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]

It sounds like you may have something automatically changing your cells.
Do you have any VBA code running in this workbook?
It would most likely be found in the particular "Sheet" module in the VB Editor, but possibly in the "ThisWorkbook" module too.
We would need to see that VBA code.

 

[ipbr21054]

No,
So ive done this.

    With ActiveSheet
    Range("D31") = Application.WorksheetFunction.Sum(Range("D5:D30"))
    Range("E31") = Application.WorksheetFunction.Sum(Range("E5:E30"))
    End With

For column D all works / sums up fine but doesnt sum up column E
The 0 in cell E31 should also be 26
Cant win sometimes..............
See screenshot

 

[kvsrinivasamurthy]

E5:E30 is having formula or manual entry.

 

[ipbr21054]

Manual, I type the value in an E cell & expect cell E31 to update but stays as o

 

[Joe4]

Your code should actually look like this, if you are going to use a "With" clause:

    With ActiveSheet
        .Range("D31") = Application.WorksheetFunction.Sum(Range("D5:D30"))
        .Range("E31") = Application.WorksheetFunction.Sum(Range("E5:E30"))
    End With

Note how there is a period in front of the word "Range". That is necessary to get that range to apply to after the word "With".

However, you do not need a "With" at all, if you are just applying it to the Active Sheet. If you do not specify which sheet, the code will always apply to the Active Sheet.
So you could replace what you have above with this:

Range("D31") = Application.WorksheetFunction.Sum(Range("D5:D30"))
Range("E31") = Application.WorksheetFunction.Sum(Range("E5:E30"))

If this is not doing what you want, the two most likely culprits are:
1. The values in columns D and E are actually being entered as Text, and not Numbers. The SUM function only works on values entered as Numbers.
2. Your code is moving to a different sheet before you run this section of code, so the Active Sheet is not the sheet you started on, or is not the Sheet you think it is.

Also note that by hard-coding the value in cells D31 and E31, if you change the values in D5:D30 or E5:E30 AFTER you run this code, it will not be reflected in your Sums on row 31 unless you run this code again.

 

[Joe4]

Manual, I type the value in an E cell & expect cell E31 to update but stays as o

As I said in my previous post, it will not update unless you run the code again.
Is there any reason why you want to hard-code the values on row 31 instead of inserting the formula, so it will dynamically reflect any changes?

Also, can you please post your ENTIRE VBA procedure that the code you posted is located in?
We will have a much better idea of what is going on if you show us the whole thing.

 

[ipbr21054]

@Joe4
I am working on the same sheet.
Trying out the 2 you mention above both only work for column D
Column D is formatted as currency
Column E is formatted as numbers

I tried to hardcode it as it wouldnt work for column D
I couldnt find any code that kept removing my sum code.

All i need is for something that will tot up the two columns.

How can i find any code that might be playing games with it so my sum code stays put

 

[Joe4]

Try replacing your block of code with this here, which will insert the SUM formulas into D31 and E31, instead of a hard-coded value:

    Range("D31:E31").FormulaR1C1 = "=SUM(R[-26]C:R[-1]C)"

How can i find any code that might be playing games with it so my sum code stays put

As I asked you for before, please post the entire VBA procedure that the VBA code you posted is found in!

Also, what procedure do you have in the Sheet module of the specific sheet you are working?
Of particular note would be any code named "Worksheet_Change" and "Worksheet_SelectionChange".

Note that when you are using VBA code, it is really important to have a full understanding of the code you created and what it will do and how it will interact when you do certain things.

 

[ipbr21054]

There is a delay in replies.

This is the code.
It shows "C3:D" but if i change D to E it freezes when i go down column D

As per reply 9 i havent done anything yet as i would like you to look at this.
Then advise the whole code i need to replace it with otherwise running away with replies / doing it

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim Cell As Range
    
    Set rng = Intersect(Target, Range("C3:D" & 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") = Application.WorksheetFunction.Sum(Range("D5:D30"))
    Range("E31") = Application.WorksheetFunction.Sum(Range("E5:E30"))

End Sub