Search code examples
excelvba

VBA code - error Run-time error '1004': application-defined or object-defined error

So I'm learning how to program in VBA and I've got an assignment where I have to program using the R1C1 style.

enter image description here

This is what I have to work with, and the main goal is to first do the total sales of each seller, however when I perform the code I get the run-time error '1004' (in bold), and I don't know why I'm getting it because the formula is working in excel (when I do it manually on the sheet).

The code that I have until now is this:

Sub Cookies()
'Place your code here
Dim nr As Integer, nc As Integer
nc = WorksheetFunction.CountA(Rows("6:6")) - 1
nr = WorksheetFunction.CountA(Columns("A:A")) - 2
**Cells(5, nc + 2).Resize(5, 1).FormulaR1C1 = "=SUMIF(R6C1:R16C1;RC[-1];R6C6:R16C6)"**

End Sub

Thank you for your help :)

Solution

  • When a formula is set by a VBA code for a cell, the string has to be in the default format of Excel and VBA. So the separators and other marks and data structures must be in the basic VBA format which corresponds to the Excel US language setup. As in your trial you also can't use the local language function name instead of e.g. SUMIF, as of the separator sign is the , (comma).

    "=SUMIF(R6C1:R16C1,RC[-1],R6C6:R16C6)"