VBA for calculations

[cmccabe1]

I have the below code:
VB

 Sub Calculations()Dim iHomopolymer As Variant
Dim Res As Variant
Dim rData As Range


    With Worksheets("annovar")
    'set the range
        Set rData = .Cells(4, 1).CurrentRegion
        
        'search row 1 for the column number
        iHomopolymer = Application.Match("Homopolymer", rData.Rows(1), 0)


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




        If Not IsError(Res) Then


            Select Case Res
                Case "Comprehensive Epilepsy"
                     .Cells(5, iHomopolymer).Value = "=IF(COUNTIFS(panel!B$2:B$6713,Q5,panel!C$2:C$6713,""<="" & R5,panel!D$2:D$6713, "">="" & R5),VLOOKUP(R5,panel!$C$2:$E$6713,3,1), ""No"")"


                Case "Marfan Disorder"
                    .Cells(5, iHomopolymer).Value = "=IF(COUNTIFS(panel!B$25610:B$29333,Q5,panel!C$25610:C$29333,""<="" & R5,panel!D$25610:D$29333, "">="" & R5),VLOOKUP(R5,panel!$C$25610:$E$29333,3,1), ""No"")"
            End Select
        End If
        
End With
End Sub

The user is supposed to make a selection in A2 and the value from that is used in a vlookup. Depending on the value returned to variable Res, a specific formula is used and the calculations are stored in the Homopolymer column on the annovar sheet. When I step through the code I can see that the lookup text is being passed to Res, but the formula is not working and I do not know why. I have this question posted at:

http://www.excelforum.com/excel-prog...-new-post.html

that is a long post with many more details. I have attached an example as well. Thank you.

Basically the user selects the case and Comprehensive Epilepsy is the vlookup value passed to Res, next the formula for that text is used in the Select Case in calculations. The calculated values are then stored in the Homopolymer column of annovar (which is variable in row count)... currently it is 192, but tomorrow it may be 210. Thank you.

[Bob Phillips]

It is a long post and the responses are many and detailed. It would be pointless us trying to a) start afresh here, b) try and follow what is happening there ad diffuse the responses here as well as there.

[cmccabe1]

I know the issue is the formulas, I just have no idea how to fix them. Thanks.

[Paul_Hossler]

I'm not as good with WS formulas as a lot of the people here, but in the VBA try ...

Cells(5, iHomopolymer).Formula = .......

Now that assumes that the formula you've constructed is correct