Excel VBA with matches and index

Question

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

Please, format your code properly and explain, what kind of errors you're getting. — Eduard Malakhov, Dec 23 '17 at 17:18
the error on the first "mydate" is unable to get the match property of the worksheetfunction class vba. At this point the code is getting break . Howwever on debugging that specific line the value for myvalue is correctly appearing. — aji, Dec 23 '17 at 18:53

score 3 · Accepted Answer · 2017-12-24T23:22:42.207

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

`Range("B1:Q1")` should be `.Range("B1:Q1")` in two places. Edited and fixed above. — , Dec 24 '17 at 23:22

Excel VBA with matches and index

1 Answers1