• Status: Solved
• Priority: Medium
• Security: Private
• Views: 9
• Last Modified:

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))
``````
0
sq30
Asked:
• 3
• 2
1 Solution

Commented:
SQ30,

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

Saurabh...
0

Author Commented:
Saurabh "V" please
0

Commented:
You don't need a loop statement you..can simply do this...

``````Dim lrow As Long

lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Range("V3:V" & lrow).Formula = "=IF(IF(ISERROR(VLOOKUP(Q3,dataLU1!\$D\$1:\$D\$200,1,0)),"""",Q3)="""",""x xx ""&U3,A3)"
``````

Saurabh...
0

Commented:
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)"
``````
Regards
0

Author Commented:
Thank you and I can't believe how fast that works.
0

Author Commented:
Sorry Rgonzo1971 I've just noticed your comment but Saurabh has worked perfect for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

• 3
• 2
Tackle projects and never again get stuck behind a technical roadblock.