Excel VBA with matches and index

I've written some VBA code with two matches and index formula. I need to pick the unique value from a sheet and compare it with the other sheet. However it is not working. I get some errors. (unable to get the match property of the worksheetfunction class vba - this is the error)

Here is my code :

Sub Post_Attendance()

Sheets("DB").Activate

'On Error Resume Next


Dim myvalue As String
Dim mydate As String
Dim mypost As String

(the date value entered in a cell)
Dim Dt As String
Dt = Range("C7").Value

(the unique id entered in a cell)

Dim empid As String
empid = Range("C8").Value

(activating another worksheet , from a cell value)
Dim strWsName As String
strWsName = Sheets("DB").Range("A7")
Sheets(Left(strWsName, 3)).Select

(match function to find the row and columns number for indexing)
mydate = WorksheetFunction.Match(Dt, Range("B1:Q1"), 0)

myvalue = WorksheetFunction.Match(empid, Range("A5:A500"), 0)

mypost = WorksheetFunction.Index(Range("B2:Q6"), myvalue, mydate)

End Sub

Answer

First off, WorksheetFunction.Match never returns a string; it either returns a number (a long integer) or an error. It is not the value from the match, it is the row or column number where the match was found.

Next, you cannot catch an #N/A error from no match with WorksheetFunction.Match but you can catch it with Application.Match into a variant.

Real dates are numbers, not strings. The raw underlying value is another long integer; e.g. a positive whole number with no decimal portion. If you had time or a datetime then you would have a decimal portion.

Resolve and reference your parent worksheet properly; do not rely upon Select or Activate.

The number returned from MATCH is the position within the range of cells searched. You are looking for a row number from row 5 to row 500 then using that to find a row within row 2 to 6; any match above row 9 (match returning 6 or above) in the original is going to be out-of-range.

If the empid values are numbers then deal with numbers; you cannot find a match to a true number from text-that-looks-like-a-number; e.g. 99 <> "99". I'm going to assume that empid should be alphanumeric and not a true number but given the errors with the previous variable assignments, it is up to you to determine the correct assignment.

Here is my best guess at an error controlled sub procedure (given that you have shown no sample data).

Option Explicit

Sub Post_Attendance()

'On Error Resume Next

    Dim myvalueRow As Variant, mydateCol As Variant, dt As Long, empid As String, mypost As Variant

    dt = Worksheets("DB").Range("C7").Value2

    empid = Worksheets("DB").Range("C8").Value2

    With Worksheets(Left(Worksheets("DB").Range("A7").Value2, 3))
        'locate the column for the date
        mydateCol = Application.Match(dt, .Range("B1:Q1"), 0)
        If IsError(mydateCol) Then _
            mydateCol = Application.Match(CStr(Worksheets("DB").Range("C7").Value2), .Range("B1:Q1"), 0)
        If IsError(mydateCol) Then
            Debug.Print "dt not found in row 1"
            Exit Sub

        End If

        'locate the row for the value
        myvalueRow = Application.Match(empid, .Columns("A"), 0)
        If IsError(myvalueRow) Then
            Debug.Print "empid not found in column A"
            Exit Sub
        End If

        mypost = Application.Index(.Range("B:Q"), myvalueRow, mydateCol)

   End With

End Sub