formula to vba

[cmccabe]

I am having trouble converting a manual formula into VBA:

The formula below worked great when all the values were on one sheet:

 =IF(COUNTIFS( A$2:A$6713,F2,B$2:B$6713,"<="&G2,C$2:C$6713, ">="&G2),VLOOKUP(G2,$B$2:$D$6713,3,1),"No")

Now I am trying to create a VBA with the values on two sheets, below is what I have:
VB

 Const myEpilepsy As String = "=IF(SUMPRODUCT(--(Panel!$B$2:$B$6713=Annovar!$Q$5),--(Panel!$C$2:$C$6713<=Annovar!$R$5),--(Panel!$D$2:$D$6713>Annovar!$R$5)),VLOOKUP(Annovar!$R$5,Panel!$C$2:$E$6713,3,1),""No"")"

Thanks.

 

[cmccabe]

If I change the underlined line of code to match the original formula, I get application define or object defined error on the line in bold. Thanks.

 Sub Calculations()Dim l As Long
Dim Res As Variant


    Res = Application.VLookup(Range("A2").Value, Range("CA:CF"), 6, 0)


[U]    Const myEpilepsy As String = "=IF(COUNTIFS(--(Panel!$B$2:$B$6713=$Q$5),--(Panel!$C$2:$C$6713<=$R$5),--(Panel!$D$2:$D$6713>$R$5)),VLOOKUP($R$5,Panel!$C$2:$E$6713,3,1),""No"")"[/U]
    Const myMarfan As String = "=IF(SUMPRODUCT(--(Panel!$B$25610:$B$29333=$Q$5),--(Panel!$C$25610:$C$29333<=$R$5),--(Panel!$D$25610:$D$D29333>$R$5)),VLOOKUP($R$5,Panel!$C$25610:$E$29333,3,1),""No"")"


    If Not IsError(Res) Then
         l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row


        Select Case Res
            Case "Comprehensive Epilepsy"
[B]                Sheets("annovar").Range("AQ5:AQ" & l).Formula = myEpilepsy[/B]
                 
            Case "Marfan Disorder"
               Sheets("annovar").Range("AQ5:AQ" & l).Formula = myMarfan
        End Select


    End If
    
End Sub

 

[cmccabe]

Should R1C1 reference style be used? Thanks.

So if I was writing this formula in R1C1
<!-- BEGIN TEMPLATE: bbcode_code -->

<CODE> Const myEpilepsy As String = "=IF(SUMPRODUCT(--(Panel!$B$2:$B$6713=Annovar!$Q$5),--(Panel!$C$2:$C$6713<=Annovar!$R$5),--(Panel!$D$2:$D$6713>Annovar!$R$5)),VLOOKUP(Annovar!$R$5,Panel!$C$2:$E$6713,3,1),""No"")"</CODE></PRE>
<!-- END TEMPLATE: bbcode_code -->it would be:
<!-- BEGIN TEMPLATE: bbcode_code -->

<CODE> Const myEpilepsy As String = "=IF(SUMPRODUCT(--(Panel!$R[2]$R[2]:$R[2]$R[6713]=Annovar!$R[17]$R[5]),--(Panel!$R[3]$R[2]:$R[3]$R[6713]<=Annovar!$R[18]$R[5]),--(Panel!$R[4]$R[2]:$R[4]$R[6713]>Annovar!$R[18]$R[5])),VLOOKUP(Annovar!$R[18]$R[5],Panel!$R[3]$R[2]:$R[5]$R[6713],3,1),""No"")"</CODE></PRE>