1

I have a small question regarding the following piece of code:

Worksheets("Sheet1").Range("A1").Formula = "=RANK(AA12" & CStr(Application.International(xlListSeparator)) & "AA12:AA23)"

You can safely assume that "Sheet1" exists. The code gives me Application-defined or object-defined when I execute the code. I was wondering what is wrong. I think my formula has been defined correctly.

Snowflake
  • 2,869
  • 3
  • 22
  • 44

2 Answers2

1

Unless you are using the .FormulaLocal property, simply use EN-US syntax, functions and regional list separators with .Formula or .FormulaR1C1. They will adjust accordingly on the worksheet.

Worksheets("Sheet1").Range("A1").Formula = "=RANK(AA12, AA12:AA23)"
0

Are you executing this code in same excel where you are writing the code? If this has to be executed in different excel file then you have to mention name of excel file. Workbooks().Worksheets("Sheet1").Range("A1").Formula = "=RANK(AA12" & CStr(Application.International(xlListSeparator)) & "AA12:AA23)"

mmehta
  • 103
  • 8
  • mmehta, please see [this](https://stackoverflow.com/questions/42583139/error-1004-formulalocal-with-seerro/42584609?s=1|64.7569#42584609) and [this](https://stackoverflow.com/questions/35724156/different-languages-issue-when-inserting-formula-from-vba/35724386?s=1|0.0000#35724386) and [this](https://stackoverflow.com/questions/28557438/excel-vba-run-time-error-1004-when-inserting-or-value-formula-into-cell/28557516?s=2|0.0000#28557516) and [this](https://stackoverflow.com/questions/32319125/vba-code-string-to-cell-not-working-run-time-error-1004/32319224?s=3|0.0000#32319224) and ... etc. –  Mar 28 '18 at 06:24