Application or Object defined error for long FormulaR1C1 , can't figure it out

[logandiana]

Can't figure this one out. Hopefully something simple.

In column AN, the formula works the way it should.
=IFERROR(IF(AND(ISNUMBER(MATCH(CONCATENATE("0000000",F2,B2),$AF:$AF,0)),NOT(ISNUMBER(MATCH(F2,$S:$S,0)))),"Returned",IF(ISNUMBER(MATCH(F2,$K:$K,0)),"Sold",IF(INDEX(SAPCrosstab4,MATCH(F2,$S:$S,0),2)="R2","R2-ReturnedDamaged",IF(INDEX(SAPCrosstab4,MATCH(F2,$S:$S,0),2)="F2","F2-FieldScrap",IF(INDEX(SAPCrosstab4,MATCH(F2,$S:$S,0),2)="L0","L0-Lost","NotReturned"))))),"NotReturned")



I used the macro recorder to help with the code to copy down approximately 8000 lines in column AN.

The recorder gave me:

.FormulaR1C1 = "=IFERROR(IF(AND(ISNUMBER(MATCH(CONCATENATE(""0000000"",RC[-34],RC[-38]),C32,0)),NOT(ISNUMBER(MATCH(RC[-34],C19,0)))),""Returned"",IF(ISNUMBER(MATCH(RC[-34],C11,0)),""Sold"",IF(INDEX(SAPCrosstab4,MATCH(RC[-34],C19,0),2)=""R2"",""R2-ReturnedDamaged"",IF(INDEX(SAPCrosstab4,MATCH(RC[-34],C19,0),2)=""F2"",""F2-FieldScrap"",IF(INDEX(SAPCrosstab4,MATCH(RC[-34],C19,0)=""L0"",""L0-Lost"",""NotReturned""))))),""NotReturned"")"

So I plugged that in with the rest to get:

Dim bi As Worksheet
Dim LR1 As Long
Set bi = ThisWorkbook.Worksheets("BI_Data")
LR1 = bi.Range("SAPCrosstab1").Rows.Count
With bi
    .Range("AN2:AN" & LR1).FormulaR1C1 = "=IFERROR(IF(AND(ISNUMBER(MATCH(CONCATENATE(""0000000"",RC[-34],RC[-38]),C32,0)),NOT(ISNUMBER(MATCH(RC[-34],C19,0)))),""Returned"",IF(ISNUMBER(MATCH(RC[-34],C11,0)),""Sold"",IF(INDEX(SAPCrosstab4,MATCH(RC[-34],C19,0),2)=""R2"",""R2-ReturnedDamaged"",IF(INDEX(SAPCrosstab4,MATCH(RC[-34],C19,0),2)=""F2"",""F2-FieldScrap"",IF(INDEX(SAPCrosstab4,MATCH(RC[-34],C19,0)=""L0"",""L0-Lost"",""NotReturned""))))),""NotReturned"")"
End With

But when I step through I get a 1004 , application defined or object defined error.
I can't figure why its not working though. Is the formula to long or something?

 

[Jonmo1]

I think it's the length of the formula. 255 characters max for an R1C1 style formula.

Try just using .Formula, and put in the formula exactly as you would in a cell by hand (as you posted it that works the way it should).
You need only to put double quote marks around each text string, just like the macro recorder formula did.

 

[logandiana]

I think it's the length of the formula. 255 characters max for an R1C1 style formula.

Try just using .Formula, and put in the formula exactly as you would in a cell by hand (as you posted it that works the way it should).
You need only to put double quote marks around each text string, just like the macro recorder formula did.

I just now noticed what happened right before I read your response. So will the macro recorder not capture over 255 characters? I went back and looked at my formula versus the R1C1 that the recorder created and it cut a few things out.
I went back in manually and added them in and now it works just as it should!

 

[Jonmo1]

Good deal.

The Macro Recorder is a great 'Tool'. But that's all it is.
It should be used as a tool to learn the syntax of certain things. It should not be the ultimate generator of your code.

So If I'm thinking 'How do I put a formula in a range of cells'
The recorder will show me basic syntax. But I wouldn't use it for that specific formula.
I learned the syntax is
Range("A1:A10").FormulR1C1 = "=blah blah"
It's now only the blah blah that I have to fill in myself.

You seem to already know this, surely the macro recorder didn't show you this
.Range("AN2:AN" & LR1).

Anyway, just some thoughts.