vba: How to make FormulaR1C1 dynamic

[Xceller]

Thank you in advance for your help!

I want replace 3963 with Lr1 and 3752 with Lr2.

Sub ChkMatchNative()

Dim Lr1 As Long
Dim Lr2 As Long



Lr1 = Cells(Rows.Count, "A").End(xlUp).Row
Lr2 = Cells(Rows.Count, "F").End(xlUp).Row


Range("D3").Resize(Lr1, 1).FormulaR1C1 = "=IF(RC3=SUMPRODUCT((R3C7:R3963C7=RC2)*(R3C9:R3963C9=RC3),R3C9:R3963C9),""X"","""")"
Range("J3").Resize(Lr2, 1).FormulaR1C1 = "=IF(RC9=SUMPRODUCT((R3C2:R3752C2=RC7)*(R3C3:R3752C3=RC9),R3C3:R3752C3),""X"","""")"



End Sub

 

[pgc01]

Hi

Try:

Range("D3").Resize(Lr1, 1).FormulaR1C1 = "=IF(RC3=SUMPRODUCT((R3C7:R" & Lr1 & "C7=RC2)*(R3C9:R3963C9=RC3),R3C9:R[COLOR=#000000]" & Lr1 & "[/COLOR]C9),""X"","""")"

 

[northwolves]

Try:

Sub ChkMatchNative()

Dim Lr1 As Long, Lr2 As Long



Lr1 = Cells(Rows.Count, "A").End(xlUp).Row
Lr2 = Cells(Rows.Count, "F").End(xlUp).Row


Range("D3").Resize(Lr1, 1).FormulaR1C1 = "=IF(RC3=SUMPRODUCT((R3C7:R" & Lr1 & "C7=RC2)*(R3C9:R" & Lr1 & "C9=RC3),R3C9:R" & Lr1 & "C9),""X"","""")"
Range("J3").Resize(Lr2, 1).FormulaR1C1 = "=IF(RC9=SUMPRODUCT((R3C2:R" & Lr2 & "C2=RC7)*(R3C3:R" & Lr2 & "C3=RC9),R3C3:R" & Lr2 & "C3),""X"","""")"



End Sub

Hope it helps.

 

[Xceller]

Try:

Sub ChkMatchNative()

Dim Lr1 As Long, Lr2 As Long



Lr1 = Cells(Rows.Count, "A").End(xlUp).Row
Lr2 = Cells(Rows.Count, "F").End(xlUp).Row


Range("D3").Resize(Lr1, 1).FormulaR1C1 = "=IF(RC3=SUMPRODUCT((R3C7:R" & Lr1 & "C7=RC2)*(R3C9:R" & Lr1 & "C9=RC3),R3C9:R" & Lr1 & "C9),""X"","""")"
Range("J3").Resize(Lr2, 1).FormulaR1C1 = "=IF(RC9=SUMPRODUCT((R3C2:R" & Lr2 & "C2=RC7)*(R3C3:R" & Lr2 & "C3=RC9),R3C3:R" & Lr2 & "C3),""X"","""")"



End Sub

Hope it helps.

northwolves & pgc01

Thank you all for your help. It works great now.