PDA

View Full Version : need help with VBA for copying highlighted rows



cmccabe1
05-15-2014, 07:00 AM
I am using the multi-step code below and am having trouble on the last few steps. Currently, the VBA will classify, highlight according to the classification, and create 2 new sheets based off of a value. Is there a way to copy and paste specific highlighted rows from the main sheet (annovar), to one off the two newly created sheets? The two new sheets are depended on the value in A2 and get text Known or Unknown put after them. Thank you very much.

The code below runs up until the last step, in an attempt to accomplish the copy and paste, but it doesn't seem to work.

Copy and paste from annovar to TestName Known:
Dark Red ‘9
Magenta ‘7
Blue ‘5
Cyan ‘8

Copy and paste to TestName Unknown:
Yellow ‘6
Pink ‘22
Purple ‘21

Above is the general and a specific example would be:
In the attached workbook row 4 in annovar is dark red, so the entire row would be copied to TestName Known.
In the attached workbook row 5 in annovar is yellow, so the entire row would be copied to TestName Unknown.




Option Explicit
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
Dim sheet_name_to_create As String
Dim rep As Integer
Dim B2 As Integer
Dim aCell As Range
Dim RowColor As Range

'set the range
Set rData = Worksheets("annovar").Cells(1, 1).CurrentRegion

'search row 1 and define criteria
With Application.WorksheetFunction
iName = .Match("Name", rData.Rows(1), 0)
iGender = .Match("Gender", rData.Rows(1), 0)
iInheritance = .Match("Inheritance", rData.Rows(3), 0)
iPopFreqMax = .Match("PopFreqMax", rData.Rows(3), 0)
iClinvar = .Match("ClinVar", rData.Rows(3), 0)
iCommon = .Match("Common", rData.Rows(3), 0)
iClassification = .Match("Classification", rData.Rows(3), 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, 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
sheet_name_to_create = Sheet1.Range("A2").Value & " Known"
GoSub AddSheet
sheet_name_to_create = Sheet1.Range("A2").Value & " Unknown"
GoSub AddSheet
Exit Sub
AddSheet:
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
MsgBox "This sheet already exists!": GoTo GotIt: End If

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = _
sheet_name_to_create
GotIt: Return


Next
'transfer classifications
Set aCell = Sheet1.Range("A4")
Do While Not IsEmpty(aCell)
'Make sure color index is correct
If aCell.Interior.ColorIndex = 3 Then
If RowColor Is Nothing Then
Set RowColor = aCell.EntireRow
Else
Set RowColor = Union(RowColor, aCell.EntireRow)
End If
End If
Set aCell = aCell.Offset(1, 0)
Loop
If Not RowColor Is Nothing Then
RowColor.Copy Sheet2.Range("A2")
End If




End Sub

ranman256
05-15-2014, 08:03 AM
At 1st glance I dont see a RETURN at the bottom of Sub: AddSheet.
If the code goes thru the entire sub (skipping the GotIt: Return in the IF) , it will never return to the main code.

Is that a factor?

cmccabe1
05-15-2014, 08:27 AM
That probably is a factor, but is that the correct code to use for what I am trying to do? For example, the 2 new sheets that are created are dependent on the value in A2. Since that will be different every time, Im not sure how to code that in. Thanks.