2

I've figured out how to insert a formula into a range of cells and managed to make it work once. Unfortunately, I can't get it to work with this formula. Instead I get an

Application-defined or object-defined error.

Here's what I'm attempting to run.

Sheets("P&L").Select
Range("A1:A250").Select
Selection.FormulaR1C1 = "=IF(ISNUMBER(LEFT(RC[+1],4)*1),LEFT(RC[+1]4,4)*1,)"
Selection.Columns.AutoFit

I suspect it has something to do with the * acting as a wildcard. I've put it in block quotes, but that just gives another error.

Any help is appreciated.

dwirony
  • 5,487
  • 3
  • 21
  • 43
toucansame
  • 164
  • 1
  • 14

1 Answers1

3

You've got an extra 4 in that formula.

Selection.FormulaR1C1 = "=IF(ISNUMBER(LEFT(RC[+1],4)*1),LEFT(RC[+1],4)*1, text(,))"
  • Just beat me to it! – dwirony Mar 22 '18 at 20:09
  • What does the `+` do in `RC[+1]`? Is that the same as `RC[1]`? (It's a little tricky to search Google for a `+` in a string) – BruceWayne Mar 22 '18 at 20:10
  • 1
    Yes @BruceWayne, I suppose it follows the same syntax as `RC[-1]`. –  Mar 22 '18 at 20:11
  • Why does every one `Select` and `Selection` all the time in code? `Sheets("P&L").Range("A1:A250").FormulaR1C1 = "..."` and if you want to use that range more than once, set it to a variable `Set myPreferredRange = Sheets("P&L").Range(...)` – AJD Mar 23 '18 at 06:31
  • @AJD I would say it's due to user inexperience. I've only just begun working with VBA in a real way and have found it very difficult to learn. I had a much easier time picking up MySQL than I've had with VBA. – toucansame Apr 14 '18 at 05:43
  • @AJD - Most peoples' first experience with 'macros' comes with the Macro Recorder which heavily uses Select, Selection, ActiveCell, etc. Hopefully, once someone graduates from recording 'macros' to writing sub procedures, they will take the time to read and heed [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/28700020). –  Apr 14 '18 at 05:58
  • @jeeped - Thanks for that link, will certainly read and attempt to heed. – toucansame Apr 14 '18 at 12:36