VBA: Recorded Formula in Macro Not Working in VBA

[datastudent]

Hi,

I recorded a Macro adding a formula in my Sheet. However when I tried to run it through VBA its not working and getting an error. Please help me troubleshoot.

Formula:
=LET(b,Note!$F$2:$F$80,c,Note!$E$2:$E$80,mf,{"Mobile","Fixed"},XLOOKUP(A2,c,b,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A2)),mf,"",0),0))


Range("C2").Formula2R1C1 = _
"=LET(b,Note!R2C6:R80C6,c,Note!R2C5:R80C5,mf,{""Mobile"",""Fixed""},XLOOKUP(RC[-2],c,b,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,RC[-2])),mf,"""",0),0))"
Range("C3").Select

 

[offthelip]

The technique I always use to debug the error when I have written an equation with vBA is:
Remove the equals sign from the VBA, so that you are just writing text to the cell, Then select the cell on the worksheet and manually put the equals sign back in again, EXCEL will then show you where the error is.
e.g:

Range("C2").FormulaR1C1 = _
"LET(b,Note!R2C6:R80C6,c,Note!R2C5:R80C5,mf,{""Mobile"",""Fixed""},XLOOKUP(RC[-2],c,b,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,RC[-2])),mf,"""",0),0))"

 

[Fluff]

You need to change the name of the variable c if you are using R1C1 references.

 

[datastudent]

The technique I always use to debug the error when I have written an equation with vBA is:
Remove the equals sign from the VBA, so that you are just writing text to the cell, Then select the cell on the worksheet and manually put the equals sign back in again, EXCEL will then show you where the error is.
e.g:

Range("C2").FormulaR1C1 = _
"LET(b,Note!R2C6:R80C6,c,Note!R2C5:R80C5,mf,{""Mobile"",""Fixed""},XLOOKUP(RC[-2],c,b,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,RC[-2])),mf,"""",0),0))"

I tried it and I'm getting that error message you get when the formula is incorrect then when I click ok it highlight R80C6.

I still don't understand

 

[datastudent]

You need to change the name of the variable c if you are using R1C1 references.

I tried to change it to "a" and or any other word but still not working

 

[Fluff]

In that case try

Range("C2").Formula2 = _
"=LET(b,Note!$F$2:$F$80,c,Note!$E$2:$E$80,mf,{""Mobile"",""Fixed""},XLOOKUP(A2,c,b,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A2)),mf,"""",0),0))"

 

[datastudent]

In that case try

Range("C2").Formula2 = _
"=LET(b,Note!$F$2:$F$80,c,Note!$E$2:$E$80,mf,{""Mobile"",""Fixed""},XLOOKUP(A2,c,b,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,A2)),mf,"""",0),0))"

It worked! Thank you so much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[jkpieterse]

Just to clear up the mystery: You have to change c in all places in the formula. This code works for me:

    Range("C2").Formula2R1C1 = _
    "=LET(blabla,Note!R2C6:R80C6,chacha,Note!R2C5:R80C5,mf,{""Mobile"",""Fixed""},XLOOKUP(RC[-2],chacha,blabla,XLOOKUP(TRUE,ISNUMBER(SEARCH(mf,RC[-2])),mf,"""",0),0))"