Run-time error '1004' when applying formula to Range.FormulaR1C1 in VBA

1
votes

I'm trying to apply a big nested formula to a range using the code below. Basically, if the value in cell A of the active row exists in the column A of another workbook and if the cell in column E of the active row is not empty, I want the active cell to display the cells to display the value of the equivalent cell in a separate workbook.

This needs to be applied to several worksheets so I'm using the variables lrow (which is an int with the last row of the active worksheet in workbook#1) and tlrow (which is an int equal to the last row of the active worksheet in workbook#2). When I step through the sub, these variables both return the numbers I would expect them to.

Likewise, this is inside of a for loop so I also use Worksheets(i).Name where I is an int.

When I run the code, I get the run-time error "'1004': Application-defined or object-defined error".

I'm assuming it's a syntax issue.

Code:

Range("B15:B" & lrow).FormulaR1C1 = _
    "=IF(OR(RC1="""",RC5=""""),"""",IF(ISERROR(VLOOKUP(RC1,'[temp.xlsx]" & _
    Worksheets(i).Name & _
    "'!A15:D" & tlrow & ",3,FALSE)),""0"",VLOOKUP(RC1,'[temp.xlsx]" & _
    Worksheets(i).Name & "'!A15:D" & tlrow & ",3,FALSE))))"
3
vbaexcel
Is the error generated on the line you've given? Have you tried using a simple formula first (such as "=2+2", then building it up? – tospig
I think you've kinda mixed things up between R1C1 and A1 notation when you construct your formula. If you use R1C1 notation, it should be consistent throughout the formula. – L42

3 Answers

0
votes

Try using this:

Range("B15:B" & lrow).FormulaR1C1 = _
    "=IF(OR(RC1="""",RC5=""""),"""",IF(ISERROR(VLOOKUP(RC1," & _
    Worksheets(i).Range("A1:D" & lrow).Address(ReferenceStyle:=xlR1C1, External:=True) & _
    ",3,FALSE)),""0"",VLOOKUP(RC1," & _
    Worksheets(i)..Range("A1:D" & tlrow).Address(ReferenceStyle:=xlR1C1, External:=True) & _
    ",3,FALSE)))"
0
votes

What version of Excel are you running? In more recent versions you can use the Iferror function in this formula to really chop down the size.

It would be something like this:

Range("B15:B" & lrow).FormulaR1C1 = _
"=IF(OR(RC1="""",RC5=""""),"""",IFERROR(VLOOKUP(RC1," & " & Worksheets(i).Range("A1:D" & _
tlrow).Address(ReferenceStyle:=xlR1C1, External:=True) & ",3,0),""0"")"
0
votes

Thanks for your help. I was able to resolve the problem by defining my vlookup range in a Range variable and then inputting the variable name in L42's equation in place of 

 worksheets(i).Range("A1:D" & lrow)

Really apprecaite the responses! Thanks again.