cmccabe1
02-03-2015, 12:17 PM
The code below is broken-up into 3 sections (the first two in bold work), it is the last I am struggling with. Now that the query has been moved to sheet panel I can not get the calculations to work, "do I need to reference the INDEX/MATCH here
Res = Application.VLookup(Range("A2").Value, wsLookUp.Range("J:O"), 6, 0) ? - I'm not sure how to assign a value to the variable Res or if that is even the problem? Thank you :)
' Add addional selection
With Sheets("panel")
Dim LastRowNo As Long
LastRowNo = .Cells(Rows.Count, "J").End(xlUp).Row
formula = "=panel!$J$1:$J$" & lastrow
End With
Worksheets("annovar").Range("B2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(2))”
Worksheets("annovar").Range("C2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(3))”
Worksheets("annovar").Range("D2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(4))”
Worksheets("annovar").Range("E2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(5))”
Worksheets("annovar").Range("F2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(6))”
' Add Inheritance
Set wsLookUp = Sheets("panel")
With Sheets("annovar")
For Each rngCell In .Range("C5", .Range("C" & Rows.Count).End(xlUp))
If WorksheetFunction.CountIf(wsLookUp.Range("G:G"), rngCell.Value) > 0 Then
rngCell.Offset(0, 1).Value = WorksheetFunction.VLookup(rngCell.Value, wsLookUp.Range("G:H"), 2, 0)
Else
rngCell.Offset(0, 1).Value = "Item not found"
End If
Next rngCell
' Add calculations based on panel
Set wsLookUp = Nothing
Res = Application.VLookup(Range("A2").Value, wsLookUp.Range("J:O"), 6, 0) ' do I need to reference the INDEX/MATCH here? - I'm not sure how
Const EpilepsyH = "=IF(COUNTIFS(panel!B2:B6712,Q5,panel!C2:C6712,""<=""&R5,panel!D2:D6712, "">=""&R5),VLOOKUP(R5,panel!C2:E6712,3,1),""No"")"
Const EpilepsyS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(panel!B6714:B7731,Q5,panel!C6714:C7731,"">=""&R5,panel!E6714:E7731, ""<=""&R5)+COUNTIFS(panel!B6714:B7731,Q5,panel!D6714:D7731,""<=""&R5,panel!F6714:F7731,"">=""&R5))>0)"
Const EpilepsyP = "=IF(COUNTIFS(panel!B7732:B25608,Q5,panel!C7732:C25608,""<=""&R5,panel!D7732:D25608,"">=""&R5),VLOOKUP(R5,panel!C7732:E25608,3,1),""No"")"
Const MarfanH = "=IF(COUNTIFS(Panel!B25609:B29333,Q5,Panel!C25609:C29333,""<=""&R5,Panel!D25609:D29333, "">=""&R5),VLOOKUP(R5,Panel!C25609:E29333,3,1),""No"")"
Const MarfanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(Panel!B29334:B29916,Q5,Panel!C29334:C29916,"">=""&R5,Panel!E29334:E29916, ""<="" & R5)+COUNTIFS(Panel!B29334:B29916,Q5,Panel!D29334:D29916,""<="" &R5,Panel!F29334:F29916,"">=""&R5))>0)"
Const MarfanP = "=IF(COUNTIFS(Panel!B29917:B47793,Q5,Panel!C29917:C47793,""<=""&R5,Panel!D29917:D47793,"">=""&R5),VLOOKUP(R5,Panel!C29917:E47793,3,1),""No"")"
Const NoonanH = "=IF(COUNTIFS(Panel!B47794:B49540,Q5,Panel!C47794:C49540,""<=""&R5,Panel!D47794:D49540, "">=""&R5),VLOOKUP(R5,Panel!C47794:E49540,3,1),""No"")"
Const NoonanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(Panel!B49541:B49755,Q5,Panel!C49541:C49755,"">=""&R5,Panel!E49541:E49755, ""<=""&R5)+COUNTIFS(Panel!B49541:B49755,Q5,Panel!D49541:D49755,""<=""&R5,Panel!F49541:F49755,"">=""&R5))>0)"
Const NoonanP = "=IF(COUNTIFS(Panel!B49756:B67632,Q5,Panel!C49756:C67632,""<=""&R5,Panel!D49756:D67632,"">=""&R5),VLOOKUP(R5,Panel!C49756:E67632,3,1),""No"")"
If Not IsError(Res) Then
l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row
Select Case Res
Case "Comprehensive Epilepsy"
Sheets("annovar").Range("AQ5:AQ" & l).formula = EpilepsyH 'Homopolymer
Sheets("annovar").Range("AR5:AR" & l).formula = EpilepsyS 'Splice
Sheets("annovar").Range("AS5:AS" & l).formula = EpilepsyP 'Pseudogene
Case "FBN1 Only"
Application.ScreenUpdating = False
With Range("W4:W" & Range("W" & Rows.Count).End(3)(1).Row)
.AutoFilter 1, "<>FBN1"
With Range("W5:W" & Range("W" & Rows.Count).End(3)(1).Row).SpecialCells(12)
.Interior.ColorIndex = 2
.EntireRow.Interior.ColorIndex = 1
End With
End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
Case "Marfan Disorder"
Sheets("annovar").Range("AQ5:AQ" & l).formula = MarfanH 'Homopolymer
Sheets("annovar").Range("AR5:AR" & l).formula = MarfanS 'Splice
Sheets("annovar").Range("AS5:AS" & l).formula = MarfanP 'Pseudogene
Case "Noonan Syndrome"
Sheets("annovar").Range("AQ5:AQ" & l).formula = NoonanH 'Homopolymer
Sheets("annovar").Range("AR5:AR" & l).formula = NoonanS 'Splice
Sheets("annovar").Range("AS5:AS" & l).formula = NoonanP 'Pseudogene
End Select
End If
Res = Application.VLookup(Range("A2").Value, wsLookUp.Range("J:O"), 6, 0) ? - I'm not sure how to assign a value to the variable Res or if that is even the problem? Thank you :)
' Add addional selection
With Sheets("panel")
Dim LastRowNo As Long
LastRowNo = .Cells(Rows.Count, "J").End(xlUp).Row
formula = "=panel!$J$1:$J$" & lastrow
End With
Worksheets("annovar").Range("B2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(2))”
Worksheets("annovar").Range("C2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(3))”
Worksheets("annovar").Range("D2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(4))”
Worksheets("annovar").Range("E2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(5))”
Worksheets("annovar").Range("F2").formula = “=INDEX(annovar!$J:$O,MATCH($A$2,annovar!$J:$J,0),COLUMN(6))”
' Add Inheritance
Set wsLookUp = Sheets("panel")
With Sheets("annovar")
For Each rngCell In .Range("C5", .Range("C" & Rows.Count).End(xlUp))
If WorksheetFunction.CountIf(wsLookUp.Range("G:G"), rngCell.Value) > 0 Then
rngCell.Offset(0, 1).Value = WorksheetFunction.VLookup(rngCell.Value, wsLookUp.Range("G:H"), 2, 0)
Else
rngCell.Offset(0, 1).Value = "Item not found"
End If
Next rngCell
' Add calculations based on panel
Set wsLookUp = Nothing
Res = Application.VLookup(Range("A2").Value, wsLookUp.Range("J:O"), 6, 0) ' do I need to reference the INDEX/MATCH here? - I'm not sure how
Const EpilepsyH = "=IF(COUNTIFS(panel!B2:B6712,Q5,panel!C2:C6712,""<=""&R5,panel!D2:D6712, "">=""&R5),VLOOKUP(R5,panel!C2:E6712,3,1),""No"")"
Const EpilepsyS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(panel!B6714:B7731,Q5,panel!C6714:C7731,"">=""&R5,panel!E6714:E7731, ""<=""&R5)+COUNTIFS(panel!B6714:B7731,Q5,panel!D6714:D7731,""<=""&R5,panel!F6714:F7731,"">=""&R5))>0)"
Const EpilepsyP = "=IF(COUNTIFS(panel!B7732:B25608,Q5,panel!C7732:C25608,""<=""&R5,panel!D7732:D25608,"">=""&R5),VLOOKUP(R5,panel!C7732:E25608,3,1),""No"")"
Const MarfanH = "=IF(COUNTIFS(Panel!B25609:B29333,Q5,Panel!C25609:C29333,""<=""&R5,Panel!D25609:D29333, "">=""&R5),VLOOKUP(R5,Panel!C25609:E29333,3,1),""No"")"
Const MarfanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(Panel!B29334:B29916,Q5,Panel!C29334:C29916,"">=""&R5,Panel!E29334:E29916, ""<="" & R5)+COUNTIFS(Panel!B29334:B29916,Q5,Panel!D29334:D29916,""<="" &R5,Panel!F29334:F29916,"">=""&R5))>0)"
Const MarfanP = "=IF(COUNTIFS(Panel!B29917:B47793,Q5,Panel!C29917:C47793,""<=""&R5,Panel!D29917:D47793,"">=""&R5),VLOOKUP(R5,Panel!C29917:E47793,3,1),""No"")"
Const NoonanH = "=IF(COUNTIFS(Panel!B47794:B49540,Q5,Panel!C47794:C49540,""<=""&R5,Panel!D47794:D49540, "">=""&R5),VLOOKUP(R5,Panel!C47794:E49540,3,1),""No"")"
Const NoonanS = "=IF(OR(V5={""intronic"",""splicing""}),(COUNTIFS(Panel!B49541:B49755,Q5,Panel!C49541:C49755,"">=""&R5,Panel!E49541:E49755, ""<=""&R5)+COUNTIFS(Panel!B49541:B49755,Q5,Panel!D49541:D49755,""<=""&R5,Panel!F49541:F49755,"">=""&R5))>0)"
Const NoonanP = "=IF(COUNTIFS(Panel!B49756:B67632,Q5,Panel!C49756:C67632,""<=""&R5,Panel!D49756:D67632,"">=""&R5),VLOOKUP(R5,Panel!C49756:E67632,3,1),""No"")"
If Not IsError(Res) Then
l = Sheets("annovar").Range("A" & Rows.Count).End(xlUp).Row
Select Case Res
Case "Comprehensive Epilepsy"
Sheets("annovar").Range("AQ5:AQ" & l).formula = EpilepsyH 'Homopolymer
Sheets("annovar").Range("AR5:AR" & l).formula = EpilepsyS 'Splice
Sheets("annovar").Range("AS5:AS" & l).formula = EpilepsyP 'Pseudogene
Case "FBN1 Only"
Application.ScreenUpdating = False
With Range("W4:W" & Range("W" & Rows.Count).End(3)(1).Row)
.AutoFilter 1, "<>FBN1"
With Range("W5:W" & Range("W" & Rows.Count).End(3)(1).Row).SpecialCells(12)
.Interior.ColorIndex = 2
.EntireRow.Interior.ColorIndex = 1
End With
End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
Case "Marfan Disorder"
Sheets("annovar").Range("AQ5:AQ" & l).formula = MarfanH 'Homopolymer
Sheets("annovar").Range("AR5:AR" & l).formula = MarfanS 'Splice
Sheets("annovar").Range("AS5:AS" & l).formula = MarfanP 'Pseudogene
Case "Noonan Syndrome"
Sheets("annovar").Range("AQ5:AQ" & l).formula = NoonanH 'Homopolymer
Sheets("annovar").Range("AR5:AR" & l).formula = NoonanS 'Splice
Sheets("annovar").Range("AS5:AS" & l).formula = NoonanP 'Pseudogene
End Select
End If