VBA formula incorrect, not sure why

I am trying to modify a formula for use in VBA. The below worked great when all the values where on one sheet.

Original:

Please Login or Register  to view this content.

Now the values are one 2 sheets and need to be run using VBA.

Please Login or Register  to view this content.

The code above produces "No" values in all the cells that get the calculations and that should not be. In the attached spreadsheet after the classify macro is run and case 1 is selected in A2, the homopolymer column is populated with values (currently "No").

Using 43394861 (R6) should be 7G, but it is not.
The way it should work is Q6 or 1 is searched in the desired range of panel, then 43394861 (R6) is searched in the desired range of panel. Finally a vlookup is done on R6 and the result of that is what is outputted to cell AQ6.

Im not sure if the correct formula is being used in the VBA as it worked before. Thank you for any help.

When using formulas in VBA I've always ended up using FormulaR1C1 and modifying the formula to that format, not sure if yours is not working for the same reason but here;

Please Login or Register  to view this content.

you would have to change all of the R#C# numbers to fit your needs

So:

Please Login or Register  to view this content.

in R1C1 is:

Please Login or Register  to view this content.

I am learning R1C1 and how to program in that style. Also, do I need

Please Login or Register  to view this content.

before the IF? Thanks.

Well, the formula I posted was just me recording a macro of placing the formula in the spreadsheet in cell E1 I think, so all of the R1C1 reference values in there (R2C[-5]:R6713C[-5], for example) are values relative to where I placed the formula. You will need to record yourself placing the formula in the locations you want them to go, and use those references in the VBA.