0

I would like to insert formula equation to worksheet with VBA code.

In a worksheet, this code works.

=COUNTIF(L2:L101; ">"&A1)

In VBA code, I've tried following code, which returning "FALSE".

 Cells(3, 20).Formula = "=COUNTIF(L2:L101; ">"&A1)"

The problem here is that, when I enter above code line into VBA editor, it automatically adds extra space before and after '>'.

So the code will be like;

 Cells(3, 20).Formula = "=COUNTIF(L2:L101; " > "&A1)"

How can I eliminate spaces?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
LazyFox
  • 3
  • 2
  • 1
    Possible duplicate of [Excel Vba: Double quotes in formula?](https://stackoverflow.com/questions/51677210/excel-vba-double-quotes-in-formula) and [How do I put double quotes in a string in vba?](https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba). `"` should be `""` inside of strings – Slai Aug 01 '19 at 09:57
  • 3
    Aside from the double quotes you are also using a semicolon, which should be a comma in vba. So try `Cells(3, 20).Formula = "=COUNTIF(L2:L101,"">""&A1)"` – JvdV Aug 01 '19 at 09:58
  • Using `;` instead of `,` to separate arguments can be needed if your regional settings use `,` as the decimal separator instead of `.` - see [this answer](https://stackoverflow.com/a/26396154/2127508) – barrowc Aug 01 '19 at 11:00
  • 1
    @barrowc, that's not how VBA works unfortunately. When writing VBA you will always need to use the US-English conventions for the list separator, which is the comma. This will be translated in your sheet with the semicolon. – JvdV Aug 01 '19 at 11:14
  • @jvdv the [FormulaLocal](https://learn.microsoft.com/en-gb/office/vba/api/excel.range.formulalocal) and [FormulaR1C1Local](https://learn.microsoft.com/en-gb/office/vba/api/excel.range.formular1c1local) properties use formulas in accordance with the user's regional settings - including using `;` as a list separator if appropriate. The non-localised Formula properties always use US English separators though so I was incorrect to say that using the localised separators was ever needed – barrowc Aug 01 '19 at 22:24

1 Answers1

0

Try:

 Cells(3, 20).formula = "=COUNTIF(L2:L101, "">"" &A1)"

You are using Semicolon instead of , & you should use double Quotes inside the Formula for using Quotes

Mikku
  • 6,538
  • 3
  • 15
  • 38