Results 1 to 9 of 9

match error

Threaded View

  1. #1
    Forum Contributor
    Join Date
    05-09-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2016
    Posts
    543

    match error

    I am getting the following error using the code below:
    Unable to get the match property of the worksheetfunction class.

    Thanks .

     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
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vlookup to Pivot Table, using Match function, returns error if can't find match value
    By AndrewHowarth in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-16-2015, 12:10 AM
  2. Match N/A error
    By westafermn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2013, 10:41 PM
  3. Match error
    By ajithjg in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 12:01 PM
  4. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  5. Error with match in VBA
    By jrm0523 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-23-2012, 01:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1