CODEWITHSUNDEEP
excelvbaformulaexcel-r1c1-notation
SeT
SeT

Reputation: 224

Issue with FormulaR1C1

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?

Upvotes: 1

Views: 1314

Answers (2)

VBAjake
VBAjake

Reputation: 1

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.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

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:

enter image description here

Upvotes: 3

Related Questions