#REF! Error for Dynamic Range Within a Formula VBA

[mharper90]

I'm trying to use VBA to insert and fill down a Match formula from S7 to the last row in column S on ws1, and the range of the Match formula should be A3 to the last row in column A on ws2. Here's what I have, and it sort of works, except that the resulting formula has a #REF ! error. I'm probably messing up something very simple, and would greatly appreciate the help to fix it.

Sub cpypste6()

Dim ws1     As Worksheet:  Set ws1 = ThisWorkbook.Sheets("Main Data")
Dim ws2     As Worksheet
Dim Lr1      As Long
Dim Lr2      As Long

Lr1 = ws1.Range("A" & Rows.Count).End(xlUp).Row

If ws1.Range("$C$4") = "1" Then
     Set ws2 = ThisWorkbook.Sheets("P1 Figure 2-2")
     Lr2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
     ws1.Range("S7:S" & Lr1).Formula = "=ISNA(MATCH(B7, 'P1 Figure 2-2'!A3:A" & Lr2 & ",0))"
     Else
EndIf

End Sub

This code gives the formula: =ISNA(MATCH(B7, 'P1 Figure 2-2'!#REF !,0))

 

[Yongle]

I cannot recreate your error -so it may be caused by the value of one of your variables

replace

ws1.Range("S7:S" & Lr1).Formula = "=ISNA(MATCH(B7, 'P1 Figure 2-2'!A3:A" & Lr2 & ",0))"

with

ws1.Range("S7:S" & Lr1).Formula = "[SIZE=4][B][COLOR=#ff0000]'[/COLOR][/B][/SIZE]=ISNA(MATCH(B7, 'P1 Figure 2-2'!A3:A" & Lr2 & ",0))"

which returns the generated formula string to the cell to help determine what the problem is

 

[mharper90]

Thanks for the trick on how to see the formula in the cell as plain text. That helped me narrow down the problem to a different macro that was clearing the existing data from ws2 after this macro runs, and then re-inputting the data back to the same location from ws1. My formula code was good all along.

 

[Yongle]

Thank for feedback