Link to home
Start Free TrialLog in
Avatar of sq30
sq30

asked on

Excel convert cell formula to vba FormulaR1C1

Hello,

I'm trying to convert the formula below to excel VBA a line of FormulaR1C1 code without success. Please help as I keep getting error 1004.

=IF(IF(ISERROR(VLOOKUP(Q3,dataLU1!$D$1:$D$200,1,0)),"",Q3)="","x xx "&U3,A3)

The formula goes in column "V"

Do

ActiveCell.FormulaR1C1 = "=IF(IF(ISERROR(VLOOKUP(RC[-5],dataLU1!R1C4:R200C4,1,0)),"",RC{[-5]="""",&""x xx""&RC[-1],RC[-21])"
ActiveCell.Offset(1, 0).Select

Loop Until IsEmpty(ActiveCell.Offset(0, -21))

Open in new window

Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

SQ30,

Quick question which Column you want to apply this formula??

Saurabh...
Avatar of sq30
sq30

ASKER

Saurabh "V" please
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

pls try
Set myrange = Range(Range("V3"), Range("V" & Range("A" & Rows.Count).End(xlUp).Row))
myrange.Formula = "=IF(IF(ISERROR(VLOOKUP(Q3,dataLU1!$D$1:$D$200,1,0)),"""",Q3)="""",""x xx ""&U3,A3)"

Open in new window

Regards
Avatar of sq30

ASKER

Thank you and I can't believe how fast that works.
Avatar of sq30

ASKER

Sorry Rgonzo1971 I've just noticed your comment but Saurabh has worked perfect for me.