Sub Calculations()
Dim iCase As Long
Dim iLastName As Long
Dim iFirstName As Long
Dim iMedicalRecord As Long
Dim iGender As Long
Dim iPanel As Long
Dim iInheritance As Long
Dim iPopFreqMax As Long
Dim iClinvar As Long
Dim iCommon As Long
Dim iClassification As Long
Dim rData As Range
Dim iRow As Long
Dim sheet_name_to_create As String
Dim rep As Integer
Dim B2 As Integer
Dim i As Long
Dim n As Long
Dim c As Double
Application.ScreenUpdating = False
'set the range
Set rData = Worksheets("annovar").Cells(1, 1).CurrentRegion
'search row and define criteria
With Application.WorksheetFunction
iCase = .Match("Case", rData.Rows(1), 0)
iLastName = .Match("Last Name", rData.Rows(1), 0)
iFirstName = .Match("First Name", rData.Rows(1), 0)
iMedicalRecord = .Match("Medical Record", rData.Rows(1), 0)
iGender = .Match("Gender", rData.Rows(1), 0)
iPanel = .Match("Panel", rData.Rows(1), 0)
iInheritance = .Match("Inheritance", rData.Rows(4), 0)
iPopFreqMax = .Match("PopFreqMax", rData.Rows(4), 0)
iClinvar = .Match("ClinVar", rData.Rows(4), 0)
iCommon = .Match("Common", rData.Rows(4), 0)
iClassification = .Match("Classification", rData.Rows(4), 0)
End With
' ClinVar Step
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If .Cells(iClinvar).Value = "benign" Then .Cells(iClassification).Value = "benign"
If .Cells(iClinvar).Value = "probable-non-pathogenic" Then .Cells(iClassification).Value = "likely benign"
If .Cells(iClinvar).Value = "unknown" Then .Cells(iClassification).Value = "uncertain significance"
If .Cells(iClinvar).Value = "untested" Then .Cells(iClassification).Value = "not provided"
If .Cells(iClinvar).Value = "probable-pathogenic" Then .Cells(iClassification).Value = "likely pathogenic"
If .Cells(iClinvar).Value = "pathogenic" Then .Cells(iClassification).Value = "pathogenic"
If .Cells(iClassification).Value = "benign" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 5 'Blue
If .Cells(iClassification).Value = "likely benign" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 8 'Cyan
If .Cells(iClassification).Value = "uncertain significance" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 6 'Yellow
If .Cells(iClassification).Value = "not provided" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 21 'Purple
If .Cells(iClassification).Value = "likely pathogenic" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 7 'Magenta
If .Cells(iClassification).Value = "pathogenic" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 9 'Dark Red
End With
Next iRow
' AD or AR Inheritance Step
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
If .Cells(iInheritance).Value = "autosomal recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
If .Cells(iInheritance).Value = "autosomal recessive" And .Cells(iPopFreqMax).Value >= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
If .Cells(iClassification).Value = "likely pathogenic" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 7 'Magenta
If .Cells(iClassification).Value = "likely benign" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 8 'Cyan
End With
Next iRow
' Common Step
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If .Cells(iInheritance).Value = "autosomal dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
If .Cells(iInheritance).Value = "autosomal recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
If .Cells(iClassification).Value = "???" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 22 'Pink
End With
Next iRow
' Gender Step
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If Cells(iGender, 2).Value = "Male" And .Cells(iInheritance).Value = "x-linked dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
If Cells(iGender, 2).Value = "Male" And .Cells(iInheritance).Value = "x-linked reessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
If Cells(iGender, 2).Value = "Male" And .Cells(iInheritance).Value = "x-linked dominant" And .Cells(iPopFreqMax).Value >= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
If Cells(iGender, 2).Value = "Male" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value >= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
If Cells(iGender, 2).Value = "Male" And .Cells(iInheritance).Value = "x-linked dominant" And .Cells(iPopFreqMax).Value <= 0.01 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
If Cells(iGender, 2).Value = "Male" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
If Cells(iGender, 2).Value = "Female" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely pathogenic"
If Cells(iGender, 2).Value = "Female" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value >= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "" Then .Cells(iClassification).Value = "likely benign"
If Cells(iGender, 2).Value = "Female" And .Cells(iInheritance).Value = "x-linked recessive" And .Cells(iPopFreqMax).Value <= 0.1 And .Cells(iClinvar).Value = "" And .Cells(iCommon).Value = "common" Then .Cells(iClassification).Value = "???"
If .Cells(iClassification).Value = "likely pathogenic" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 7 'Magenta
If .Cells(iClassification).Value = "likely benign" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 8 'Cyan
If .Cells(iClassification).Value = "???" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 22 'Pink
End With
Next iRow
' Create new workbooks based on name
Dim sAS As String
sAS = ActiveSheet.Name
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(ActiveSheet.Range("A2").Value & " Known").Delete
Worksheets(ActiveSheet.Range("A2").Value & " Unknown").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets(sAS).Range("A2").Value & " Known"
Worksheets(sAS).Rows(3).Copy ActiveSheet.Rows(1)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Sheets(sAS).Range("A2").Value & " Unknown"
Worksheets(sAS).Rows(3).Copy ActiveSheet.Rows(1)
Worksheets(sAS).Activate
' Transfer classifications
For i = 4 To Range("A" & Rows.Count).End(xlUp).Row
If Cells(i, "A").Interior.ColorIndex = 9 Or _
Cells(i, "A").Interior.ColorIndex = 8 Or _
Cells(i, "A").Interior.ColorIndex = 7 Or _
Cells(i, "A").Interior.ColorIndex = 5 Then
Rows(i).Copy Sheets(Range("A2").Value & " Known").Range("A" & Rows.Count).End(xlUp).Offset(1)
Else
Rows(i).Copy Sheets(Range("A2").Value & " Unknown").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
'Clean and sort sheets by colour
Sheets(Range("A2").Value & " Known").Activate
ActiveSheet.Range(ActiveSheet.Range("A1").End(xlToRight).Offset(0, 1), ActiveSheet.Rows(1).Cells(Rows(1).Cells.Count)).EntireColumn.Clear
ActiveSheet.Columns.AutoFit
ActiveSheet.Range("A1").Select
Sheets(Range("A2").Value & " Known").Activate
ActiveWorkbook.Worksheets("TestName Known").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TestName Known").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(128, 0, 0)
ActiveWorkbook.Worksheets("TestName Known").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 0, 255)
ActiveWorkbook.Worksheets("TestName Known").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 0, 255)
ActiveWorkbook.Worksheets("TestName Known").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(0, 255, 255)
With ActiveWorkbook.Worksheets("TestName Known").Sort
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets(Range("A2").Value & " Unknown").Activate
ActiveSheet.Range(ActiveSheet.Range("A1").End(xlToRight).Offset(0, 1), ActiveSheet.Rows(1).Cells(Rows(1).Cells.Count)).EntireColumn.Clear
ActiveSheet.Columns.AutoFit
ActiveSheet.Range("A1").Select
Sheets(Range("A2").Value & " Unknown").Activate
ActiveWorkbook.Worksheets("TestName Unknown").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("TestName Unknown").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 255, 0)
ActiveWorkbook.Worksheets("TestName Unknown").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, 128, 128)
ActiveWorkbook.Worksheets("TestName Unknown").Sort.SortFields.Add(Range("A1"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(102, 0, 102)
With ActiveWorkbook.Worksheets("TestName Unknown").Sort
.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Worksheets(sAS).Activate
Application.ScreenUpdating = True
MsgBox "The data has been formatted and transferred.", vbInformation
End Sub
Bookmarks