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)),"",Q 3)="","x xx "&U3,A3)
The formula goes in column "V"
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,
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))
ASKER
Saurabh "V" please
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
pls try
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)"
Regards
ASKER
Thank you and I can't believe how fast that works.
ASKER
Sorry Rgonzo1971 I've just noticed your comment but Saurabh has worked perfect for me.
Quick question which Column you want to apply this formula??
Saurabh...