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