PDA

View Full Version : VBA for calculations



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.

Bob Phillips
06-21-2014, 03:36 PM
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
06-21-2014, 04:18 PM
I know the issue is the formulas, I just have no idea how to fix them. Thanks.

Paul_Hossler
06-21-2014, 04:31 PM
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