Error using SUM fuction in Excek VBA code

Question

everyone!

Can someone please help me?

Sub Gomb1_Kattintás()
Dim LMonth As String
    LMonth = Month(Date)
Dim Honap As String
ws_num = ThisWorkbook.Worksheets.Count
Dim x As Integer
Dim Form As String


Select Case True
    Case (LMonth = 1)
            Honap = "Január"
    Case (LMonth = 2)
            Honap = "Február"
    Case (LMonth = 3)
            Honap = "Március"
    Case (LMonth = 4)
            Honap = "Április"
    Case (LMonth = 5)
            Honap = "Május"
    Case (LMonth = 6)
            Honap = "Június"
    Case (LMonth = 7)
            Honap = "Július"
    Case (LMonth = 8)
            Honap = "Augusztus"
    Case (LMonth = 9)
            Honap = "Szeptember"
    Case (LMonth = 10)
            Honap = "Október"
    Case (LMonth = 11)
            Honap = "November"
    Case (LMonth = 12)
            Honap = "December"
End Select

    Sheets(2).Select
    Range("B2").Select
    ActiveCell.Formula = Honap


    Form = "=SZUM("
    For x = 3 To Sheets.Count
    Form = Form & Sheets(x).Name & "'!E3;"
    Next x
    Form = Left(Form, Len(Form) - 1) & ")"
    Sheets(2).Select
    Range("E3").Formula = Form
    End Sub

This is one of my code. What I try to do is simple. I have a worksheet, that would fuction as a monthly summary. The other worksheats generated automatically with the help of another code. When I press the button on this sheet, the sheet should lookup the system date, extract the month, convert the number into name, place it in a specific cell. After that, it should run through all the other sheets from 3rd to last, and summarize the number that is in the "E3" cell in every sheet, and place the result in the summary sheets "E3" cell.

Now, my problem is, that when I try to test my code the VBA gives me a messagebox error.

Run-time error '1004': Application-defined or object-defined error

and when I press Debug; it highlights this line:

Range("E3").Formula = Form

I don't know what the problem is. I tried to copy the formula witch is created by the VBA code into that cell manually, and it works. A manually created a formula int excel, that summarize a few cells. It works. But when I copy that formula into the VBA codes last line e.g.:

Range("E3").Formula = "=SZUM('2020.04.03.'!E3;'2020.04.03. (2)'!E3)"

still got the same error.

Anyone? Any ideas?

If you're having issues, then maybe use `Range.FormulaLocal`. — BigBen, Apr 09 '20 at 12:32
I noticed something. When I run the debugging, and list what are the values of the local variables, the "Form" contains "=SUM)" I think that the problem is with the cycle, and the string removing method, but I can't see what is. — Arethan, Apr 13 '20 at 07:32
@BigBen Oh I finally got it work. It was that I forgot to add a & "'" & where I created the formula, so the code could not find the sheet, baceuse the nameing was wrong. Form = "=SZUM(" For x = 3 To Sheets.Count Form = Form & "'" & Sheets(x).Name & "'!E3;" 'Add SheetName and Cell and Comma Next x Form = Left(Form, Len(Form) - 1) & ")" 'Remove trailing comma and add parenthesis Sheets(2).Select Range("E3").FormulaLocal = Form This is how it looks and works. Thanks for the help :-) — Arethan, Apr 13 '20 at 07:45

Error using SUM fuction in Excek VBA code

0 Answers0