0

I have a problem with this VBA code in Excel:

Sub test()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ws.Cells(1, 1) = "=MAX('Sheet2'!A:A)"
    ws.Cells(1, 2) = "=AVERAGE('Sheet2'!A:A)"
    ws.Cells(1, 3) = "=PERCENTILE('Sheet2'!A:A;0,95)"
End Sub

I can insert MAX and AVERAGE functions without a problem, but when I get to the PERCENTILE, VBA throws a 1004 runtime error. I tried with PERCENTILE.EXC, and I tried Cells(1, 3).Function too. None of those work. I can't figure out what might be the problem.

When there is no equal sign at the start of the string, it works. It pastes the string in the cell no problem. As soon as it has an "=", VBA shows a runtime error.

braX
  • 11,506
  • 5
  • 20
  • 33
Hirurawa
  • 1
  • I would use `.Formula` or `.FormulaLocal` here if needed but see the linked duplicate for a discussion of the broader issue here, especially the `;`. If you can't figure it out, please comment back here and I or someone else can reopen the question. – BigBen Jun 25 '20 at 15:37
  • Thank you for pointing me to the other thread. `.FormulaLocal` works perfectly. I didn't know the `;` could cause this. Isn't the semicolon a universal separator for excel funcion parameters? – Hirurawa Jun 25 '20 at 15:52
  • No. In the US, it is the `,`. – BigBen Jun 25 '20 at 15:58

0 Answers0