The code below runs fine except when I insert the sheet_name_to_create line towards the bottom. Something seems to be wrong with the syntax, but I can not figure it out. Basically, I am trying to create one button that will classify and after that insert a new worksheet.Thanks.
Private Sub CommandButton1_Click()
Dim iGender As Long
Dim iName 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
'set the range
Set rData = Worksheets("annovar").Cells(1, 1).CurrentRegion
'search row 1 for the column number
With Application.WorksheetFunction
iGender = .Match("Gender", rData.Rows(1), 0)
iName = .Match("Name", rData.Rows(1), 0)
iInheritance = .Match("Inheritence", rData.Rows(1), 0)
iPopFreqMax = .Match("PopFreqMax", rData.Rows(1), 0)
iClinvar = .Match("ClinVar", rData.Rows(1), 0)
iCommon = .Match("Common", rData.Rows(1), 0)
iClassification = .Match("Classification", rData.Rows(1), 0)
End With
'ClinVar Step 1
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 3
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 3
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 4
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If .Cells(iGender).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).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).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).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).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).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
For iRow = 2 To rData.Rows.Count
With rData.Rows(iRow)
If .Cells(iGender).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).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 = "???" Then .Cells(iClassification).EntireRow.Interior.ColorIndex = 22 'Pink
End With
Next iRow
sheet_name_to_create = Sheet1.Range("A2").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "This sheet already exists!"
Exit Sub
End If
Next
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = sheet_name_to_create
End Sub
Bookmarks