Hey I'm trying to convert the following formula to be entered in automatically in VB
 =(SUMPRODUCT(--(HC!$A$3:$A$17=$B2),--(HC!$B$3:$B$17=$C2),HC!C$3:C$17)*INDEX(Data!C$2:C$5,MATCH($A2&"@"&$B2,INDEX(Data!$A$2:$A$5&"@"&Data!$B$2:$B$5,0),0)))/SUMIF(HC!$A$3:$A$17,$B2,HC!C$3:C$17)


Below is the code I have so far (LastRow is last row in current sheet, LR is last row in Data and LR1 is last row in HC). I get a error when running and the debugger highlights the longest line (with the formula). I have run the entry using Macro record and those results are posted below my code, the only problem is I don't know how to edit that particular format to look like my old format. I want to maintain this format so if anyone else uses my workbook it will be easier to follow


Sub FillinFormula()
    Dim LastRow As Long
    Dim LR As Long
    Dim LR1 As Long
    
    LR = Sheets("Data").Range("B" & Rows.Count).End(xlUp).Row
    LR1 = Sheets("HC").Range("B" & Rows.Count).End(xlUp).Row
    LastRow = Range("A" & Rows.Count).End(xlUp).Row


  Range("D2").Formula = "=(SUMPRODUCT(--(HC!$A$3:$A$" & LR1 & "=$B2),--(HC!$B$3:$B$" & LR1 & "=$C2),HC!C$3:C$" & LR1 & ")*INDEX(Data!C$2:C$" & LR & ",MATCH($A2&" & "@" & "&$B2,INDEX(Data!$A$2:$A$" & LR & "&""@""&Data!$B$2:$B$" & LR & ",0),0)))/SUMIF(HC!$A$3:$A$" & LR & ",$B2,HC!C$3:C$" & LR & ")"

    Range("D2:N2").FillRight
    
    With Range("D2:N" & LastRow)
        .FillDown
        .Copy
        Range("D2").PasteSpecial xlPasteValues
    End With

    Range("A2").Select
End Sub

"=(SUMPRODUCT(--(HC!R3C1:R17C1=RC2),--(HC!R3C2:R17C2=RC3),HC!R3C[-1]:R17C[-1])*INDEX(Data!R2C[-1]:R5C[-1],MATCH(RC1&""@""&RC2,INDEX(Data!R2C1:R5C1&""@""&Data!R2C2:R5C2,0),0)))/SUMIF(HC!R3C1:R17C1,RC2,HC!R3C[-1]:R17C[-1])"



I think the problem is somewhere in the fact that the formula uses quotation marks and the @ sign but those are understood differently in VBA

Any help or suggestions would be appreciated