Excel VBA insert formel RRS feed

  • Frage

  • Hello,

    got error 1004

    Any ideas?

    Sheets("Summary").Range("C3").Formula = "=SUMIF('" & SheetName & "'!C:C;'Summary'!C2;'" & SheetName & "'!D:D)"


    Dienstag, 16. Juli 2013 14:36


  • Your formula separator ";" is wrong, the formula property accepts only the English separator ",".

    And for complex formulas it is easier if you refer to the cells and create the formula like shown below.


    Sub Test()
      Dim CC As Range, DD As Range
      Dim F As String
      SheetName = ActiveSheet.Name
      'Refer to the range
      With Sheets(SheetName)
        Set CC = .Range("C:C")
        Set DD = .Range("D:D")
      End With
      'Setup the basic formula as string
      F = "=SUMIF(CC,Summary!C2,DD)"
      'Replace the placeholders with the address of the range
      F = Replace(F, "CC", CC.Address(External:=True))
      F = Replace(F, "DD", DD.Address(External:=True))
      'Store the formula
      Sheets("Summary").Range("C3").Formula = F
    End Sub

    Mittwoch, 17. Juli 2013 10:01