Issue with FormulaR1C1

Question

I need a little help with the below formula in VBA:

=IFERROR(VLOOKUP(J3,'Int Data'!D:J,7,0),"")

I've tried to run the following:

Range("AP2").Select    
ActiveCell.Formula = "=IFERROR((VLOOKUP(RC[-1],(J3,'Agent Table'!D:J,7,0),"")"    
Range("AP2").Select    
Selection.Copy    
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False    
Cells.Select    
Cells.EntireColumn.AutoFit

When I try to run this statement -

ActiveCell.Formula = "=IFERROR((VLOOKUP(RC[-1],(J3,'Agent Table'!D:J,7,0),"")"

I got a:

Run-time error '1004'

Can you please advise what is wrong with this statement?

Welcome to SO. My eyes could be wrong but I'm pretty sure that VLOOKUP has only 4 arguments, and in your formula you have 5. Your formula is `VLOOKUP(RC[-1],(J3,'Agent Table'!D:J,7,0)`. Should it not be `VLOOKUP(J3,'Agent Table'!D:J,7,0)` ??? That would fit your example of `VLOOKUP(J3,'Int Data'!D:J,7,0)`. 4 Arguments, not 5. — Foxfire And Burns And Burns, Sep 26 '18 at 15:35
Besides, parenthesis quantity must be the same in both cases always, but you have 4 `(` and only 2 `)`. Something is wrong there — Foxfire And Burns And Burns, Sep 26 '18 at 15:39

Vityata · Accepted Answer · 2018-09-26T15:43:32.823

3

You need .FormulaR1C1 and not .Formula:

ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(R3C10,'Agent Table'!C4:C10,7, 0)"""")"

R3C10 is the R1C1 notation for J3.

If you are using the Macro Recorder to see the formula, select "Use Relative References" in the developer tab to change the way the address is displayed:

edited Sep 26 '18 at 15:43

answered Sep 26 '18 at 15:02

Vityata

42,633
8
55
100

Hi Vityata, I tried your suggestion but regrettably, it doesn't work. I'm not trying to record the macro though. Thanks – SeT Sep 26 '18 at 15:10
@MarkH - I know you are not trying to record the macro. This is just in case, if you were trying to. In general, write a formula in Excel, select it and run the code from here - https://stackoverflow.com/a/49363501/5448626 It will print you the correct code in the format you select. E.g. replace `strFormula = Selection.Formula` with `strFormula = Selection.FormulaR1C1`. – Vityata Sep 26 '18 at 15:12
Thanks again Vityata but still no luck with it. Still the same error message. Any other thoughts? Thanks – SeT Sep 26 '18 at 15:22
@MarkH - what did you get as a result in the immediate window after using the function from the linked answer in my previous comment? – Vityata Sep 26 '18 at 15:28
@MarkH - actually in the formula `J3` is not passed in R1C1 format. – Vityata Sep 26 '18 at 15:32
Both reference must be in same format either R1C1 or A1 – Scott Craner Sep 26 '18 at 15:33
2

`'Agent Table'!D:J` should be `'Agent Table'!C4:C10` – Scott Craner Sep 26 '18 at 15:36
1

Also there are too many `(`: `ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(R3C10,'Agent Table'!C4:C10,7,0),"""")"` – Scott Craner Sep 26 '18 at 15:39
@ScottCraner - I have edited while improving the preivous one :) – Vityata Sep 26 '18 at 15:40
1

Still need to fix the `""` to `""""` – Scott Craner Sep 26 '18 at 15:41
Just one question. How do you know the area to search is `C4:C10` instead of `D:J`? :O – Foxfire And Burns And Burns Sep 26 '18 at 15:42
2

@FoxfireAndBurnsAndBurns C4:C10 in R1C1 is columns D:J. – Scott Craner Sep 26 '18 at 15:42
Omg!!!!! Facepalm! Thanks @ScottCraner I read that still as `A1` style. My bad. Thanks for aclaration – Foxfire And Burns And Burns Sep 26 '18 at 15:43
To be honest, since seeing this approach - https://stackoverflow.com/questions/49363434/excel-vba-formula-german-french-italian-russian-dutch-foreign-function/49363501#49363501 I have never ever tried anything else. – Vityata Sep 26 '18 at 15:44
@ScottCraner - thanks very much for rectifying my line. It does work perfectly. – SeT Sep 26 '18 at 15:50
@MarkH, If this answer worked, please, mark it as correct so it can help future users. Thanks! – Foxfire And Burns And Burns Sep 27 '18 at 09:22

score 0 · Answer 2 · answered Sep 26 '18 at 15:58

0

You are combining A1 and R1C1 referencing in the same formula, this should be avoided.

Generally R1C1 is the preferred reference style for VBA. It may require a few more keystrokes but it provides greater control when accessing the cells.

answered Sep 26 '18 at 15:58

VBAjake

1
1

Issue with FormulaR1C1

2 Answers2