formula issue in VBA

I also posted this to, but not sure if this is where it should be.
http://www.mrexcel.com/forum/excel-q...lications.html

I am having trouble converting a manual formula into VBA:

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

Code:
=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
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"")"
Thanks.

I think when placing an Excel formula in VBA you need to use R1C1 format, so G2 is actually (7,2) or something like that. I had to do it once before but don't remember the syntax exactly.

Also, if that is a CSE formula, and I'm not familiar enough with those yet to tell, I am not sure if it can be entered through VBA. I ran into that issue before and I never did find a solution.

Hope this helps!

So if I was writing this formula in R1C1

Please Login or Register  to view this content.

it would be:

Please Login or Register  to view this content.

Thanks.

Thank you.