0

I need your help. I am writing this small piece of code in VBA to use in a an RPA proces. I have tested my formula in Excel and it works, but everytime when i try to run it from VBA it crashes with error-code 1004 and tells me the problemn is in code .Range("C29").Formula = "=SUMIF(Sheet1!$A$19:$A$1000;$B29;Sheet1!$G$19:$G$1000)".

Changed the formula to simplere formulas and that works fine. Anybody else who knows this issue?

Sub FillDown()

    Dim strFormulas '(1 To 3) As Variant 

    With ThisWorkbook.Sheets("MainSheet")


        .Range("C29").Formula = "=SUMIF(Sheet1!$A$19:$A$1000;$B29;Sheet1!$G$19:$G$1000)"
 
        .Range("C29:C501").FillDown
    End With


End Sub
RandomBusinessAnalyst
  • 1
  • 2
  • 1
    This has been asked so many times and I'm too tired to search for the best duplicate answer. You need to specify the formula in US-English when writing it with VBA. Replace the `;` by `,`. – FunThomas Mar 26 '21 at 10:04
  • https://stackoverflow.com/a/51399429/7599798 – FunThomas Mar 26 '21 at 10:12

2 Answers2

0

Check your SUMIF formular. It contains error.Is your idea "=SUMIF(Sheet1!$A$19:$A$1000, $B29, Sheet1!$G$19:$G$1000)"??

judaikun88
  • 59
  • 5
0
Sub FillDown2()
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("MainSheet")
     
    ws.Range("C29").FormulaLocal = "=SUMIF(Sheet1!$A$19:$A$1000;$B29;Sheet1!$G$19:$G$1000)"
    ws.Range("C29:C501").FillDown
End Sub

So the problemn was based in the settings of my worksheet. By adding the formulalocal and at least i now dont have the bug anymore, on to the next problemn xD

RandomBusinessAnalyst
  • 1
  • 2