0

Why formulas like like iferror or left cannot be entered to excel vba?

Range("D11").Formula = "=SUM(C8,C6,C4,C2)"

Range("D12").Formula = "=IFERROR(C8,"")"

Range("D13").Formula = "=LEFT(C9,2)"

Excelworm
  • 15
  • 3
  • The first and third formulas are fine. The second needs to have the inner quotes doubled: `Range("D12").Formula = "=IFERROR(C8,"""")"` – BigBen Apr 21 '21 at 17:15
  • Not documented, but MS 365 (64bit office?) might need a fully qualified range reference ahead, like e.g. `Sheet1.Range("D11")Formula = ..."` @Excelworm – T.M. Apr 21 '21 at 17:24
  • @BigBen Never had this issue before, but seems that the 1st formula has another cause (see my comment above) – T.M. Apr 21 '21 at 17:28
  • @T.M. - no repro. That's implicitly `ActiveSheet.Range("D11").Formula ...` – BigBen Apr 21 '21 at 17:30
  • @BigBen Of course, I know. But reproduced it just now (the first time ever!) – T.M. Apr 21 '21 at 17:31
  • And the error was? @T.M. Also note that OP doesn't mention the first line throwing an error. – BigBen Apr 21 '21 at 17:33
  • @BigBen Just nothing, no reaction – T.M. Apr 21 '21 at 17:35
  • That's not an error. Most likely the `ActiveSheet` is not what you think it is. – BigBen Apr 21 '21 at 17:38
  • OP doesn't mention an error, just: *Why formulas like like iferror or left* **cannot be entered** *to excel vba*. The answer might be (at least in MS 365/64bit office): *Fully qualify the range assignment!*, it worked for me now:-) @BigBen – T.M. Apr 21 '21 at 17:42
  • @T.M. the first formula is `SUM`, not `IFERROR` or `LEFT`. I agree with you about fully qualifying, but your example of "not working" is not reproducible, and irrelevant. – BigBen Apr 21 '21 at 17:43
  • Though no implicit wrong reference by myself, it seems that my excel workbook got confused about (Code)Name renaming. If there will be no other replies, I assume it was an isolated internal bug. - Thanks for response, appreciated:-) @BigBen – T.M. Apr 21 '21 at 17:51
  • 1
    THANKS @BigBen! Thast was issue, I missed double quotes . – Excelworm Apr 21 '21 at 17:55

0 Answers0