'lookup' in FormulaR1C1 returns error 1004

Question

When I try to run this line of code, I receive VBA error 1004.

Range("K38").Select
ActiveCell.FormulaR1C1 = "=IFERROR(LOOKUP(2, 1/(COUNTIF($K$36:$K37, WIP_Table[Resource Name])=0), WIP_Table[Resource Name]), """")"

The formula for the cell works when input into Excel manually.

You are using `.FormulaR1C1` instead of `.Formula`. **Sidenote:** You are using `.Select` to get an `ActiveCell` but there is no need to do so. Have a look at [this](https://stackoverflow.com/q/10714251/9758194) older post on SO. — JvdV, Feb 03 '20 at 12:55
You've also passed three arguments to COUNTIF, which won't work. — Rory, Feb 03 '20 at 12:59
@Rory, no there is only two arguments, try it out in excel it works! It is just failing in vba though — user11015000, Feb 03 '20 at 13:10
That's how it was written in my code, I copied it across wrong. Not the source of the error anyway — user11015000, Feb 03 '20 at 13:14
@user11015000 it should be “there are only two arguments”... — Solar Mike, Feb 03 '20 at 13:24

score 3 · Accepted Answer · answered Feb 03 '20 at 13:37

3

As per my comment to close this thread:

You are using .FormulaR1C1 instead of .Formula. Sidenote: You are using .Select to get an ActiveCell but there is no need to do so. Have a look at this older post on SO.

answered Feb 03 '20 at 13:37

JvdV

70,606
8
39
70

'lookup' in FormulaR1C1 returns error 1004

1 Answers1