VBA To Use Formula On Two Sheets

Code:
iHomopolymer = Application.Evaluate("=IF(COUNTIFS(B$2:B$6713,Q5,C$2:C$6713,
""="" & R5),VLOOKUP(R5,$C$2:$E$6713,3,1), ""No"")")

The original formula is above, with a description in the middle, and an attempt at a code below.

iHomopolymer is column AQ on the annovar sheet

B$2:B$6713, C$:C$6713, D$2:D$6713 are values in the worksheet panel

Q5 and R5 are values in the worksheet annovar

then in the vlookup R5 is a value in annovar and the lookup$C$2:$E$6713,3,1 are values from the panel worksheet.

For example, it is basically matching the value in Q5 in annovar to the B2:B6713 range in panel, once a match is found it then uses the value in R5 from annovar to search the C2:C6713 and D2:D6713 range in panel (this determines a location). Finally a vlookup is performed on the R5 value from annovar in the range specified in panel and the description or column 3 results.

Code:
Dim l As Long
l = Range("AQ" & Rows.Count).End(xlUp).Row
Range("AQ5").Formula = "= IF(COUNTIFS(B$2:B$6713,Q5,C$2:C$6713,""="" & R5),
VLOOKUP(R5,$C$2:$E$6713,3,1), ""No"")"
With Range("AQ5:AQ" & l)
.FillDown
End With