function SUM vba

Question

I want to use the function SUM of different cells from different columns, I used this syntax but it doesn't work, it returns always 0:

Sub test(AC As String)
    Sheets("Feuil4").Activate
    Range("A1").Formula = "=SUM(Feuil2!AC10, -Feuil2!AC11)"
End Sub

Sub TEST()
    Call test("B")
End Sub

I also tried: Range("A1").Formula = "=SUM(Feuil2!AC & 10, -Feuil2!AC & 11)" it doesn't work either.

The following subject will help you : https://stackoverflow.com/questions/11707888/sum-function-in-vba — Paul92ilm, Jul 25 '18 at 11:29

Pᴇʜ · Answer 1 · 2018-07-25T11:36:59.987

This formula

Range("A1").Formula = "=SUM(Feuil2!AC10, -Feuil2!AC11)"

subtracts sheet Feuil2 cell AC11 from Feuil2 cell AC10.

If you want to use the AC as column name variable then you must use

Sub WriteSumFormula(SumColumn As String)
    Sheets("Feuil4").Range("A1").Formula = "=SUM(Feuil2!" & SumColumn & "10, -Feuil2!" & SumColumn & "11)"
End Sub

Also use descriptive variable and function names to make life easier and get better maintainable code. You cannot name 2 different subs TEST. Names must be unique.

Then this Test

Sub TestWriteSumFormula()
    Call WriteSumFormula("B")
End Sub

should write the formula =SUM(Feuil2!B10, -Feuil2!B11) into cell A1.

score 0 · Answer 2 · answered Jul 25 '18 at 11:32

The following should work too, please bear in mind I renamed one of your Subs as they can't both be named "TEST":

Sub TEST(AC As String)
    Sheets("Feuil4").Range("A1").Formula = "=SUM(Feuil2!" & AC & "10, -Feuil2!" & AC & "11)"
End Sub

Sub TESTS() 'renamed this
    Call TEST("B")
End Sub

function SUM vba

2 Answers2