Unable to Match property of Worksheet class function

**bambino_32** · 01-28-2010, 01:47 AM

Hi in below stated code i do get this error:
Unable to Match property of Worksheet class function.
It seems I made everything right, but I cannot figure out why this message appears. Hope you can find out the problem...

Private Sub StudentButton_Click()
    Dim DateField As Range, ResultTable As Range
    Dim StartDay As Date, EndDay As Date
    Dim lRow As Long, lColumn As Long
    Dim sDayPos As Long, eDayPos As Long
    Dim StartRow As Integer, StartColumn As Integer
    Dim MyMonth As Integer, MyYears As Integer, lDate As Integer

    StartRow = Range("FirstCell").Row
    StartColumn = Range("FirstCell").Column

    Set ResultTable = Range("ResultTable")
    Set DateField = Sheets(2).Range("Dates")
    
    'Getting Last column number
    With Range(StartRow & ":" & StartRow)
         lColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With

    For MyYears = 1 To lColumn - StartColumn
        For MyMonth = 1 To 12

----------------------------------------- SOME PART OF CODE BETWEEN ------------------------------

            'Getting positions for Fist day and last day of a month in Datafield Range
            With WorksheetFunction
                sDayPos = .Match(StartDay, DateField, 0)   <<<<<<< CAUSES ERROR
                eDayPos = .Match(EndDay, DateField, 0)    <<<<<<< CAUSES ERROR
            End With

        MsgBox sDayPos & " and " & eDayPos
        Next MyMonth
    Next MyYears
End Sub

**pike** · 01-28-2010, 03:30 AM

hi bambino_32
Try this....

with Application.WorksheetFunction
                sDayPos = .Match(StartDay, DateField, 0)   
                eDayPos = .Match(EndDay, DateField, 0)    
 End With

**Andy Pope** · 01-28-2010, 05:38 AM

The error is raised because the Match function fails to find a match.

You can use error trapping to catch no finds.

    On Error GoTo ErrMatch
    
    With Application.WorksheetFunction
        sDayPos = .Match(StartDay, DateField, 0)
    End With
    
    Exit Sub

ErrMatch:
    MsgBox "No Match"
    Exit Sub
    
End Sub

Dates are really tricking things to find. You may need to convert the date to a number.