cmccabe1
06-21-2014, 08:44 AM
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-programming-vba-macros/1018069-select-case-calculations-not-working-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.
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-programming-vba-macros/1018069-select-case-calculations-not-working-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.