Application.Match not working

[KentBurel]

I have a range that defines a master table. On another sheet I have a range that needs to be validated against the master table. The first column must be a row in the first column of the master table. Then the master table provides the maximum number for the second column. Here is a snippet of the master table.

Here is a snippet of the table I'm trying to validate.

I need to read the second table sequentially. For each row I first must validate that the first column is a row in the Precinct master table. The precinct in the first row is 1A and it is in the master table. Then the # of BMDs column in the Precinct Master is for precinct 1A. So I need to validate that the number in the second table is between 1 and 8 inclusive. You see that the # of BMDs is different for each precinct.

My code looks like this:

Option Explicit

Sub ValidateBMDMasterTable()
'
' This sub validates the BMD Master Table.  For each row it ensures these properties
'  1. The precinct number is found in the master precinct table on the constants page
'  2. The number is greater than 0.
'  3. The number is less than or equal to the number of BMDs in the master precinct table
'     on the constants page.
'
    Dim firstRow As Integer, lastRow As Integer
    Dim myPrecinct As Range
    Dim myNumber   As Range
    Dim bmdData    As Range
    Dim sortRange  As Range
    Dim bothcells  As Range
    Dim precinctArray()
    Dim myRow      As Variant
    Dim currentPrecinct As String
    Dim currentNumber   As Integer
    Dim precinctNumber  As Integer
    Dim result As Variant
    Dim myTitle As String
    Dim myMsg As String
    Dim response As Variant
  
    ActiveWorkbook.Worksheets("BMD Master").Select
   
' Update the BMDData named range in case BMDs have been added or subtracted

'   Row 3 is the first line of the BMDMaster table
    firstRow = Cells(1, 1).End(xlDown).Row + 1 ' Skip the header row
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set bmdData = Range("A" & firstRow & ":F" & lastRow)
   
   
' The bmdData named range is ready to go now.

'    ReDim precinctArray(1 To Range("Precincts").Rows.Count, 6) ' Precincts is a named range
    precinctArray = Range("Precincts")
   
    For Each myRow In bmdData.Rows
        currentPrecinct = myRow.Cells.Item(1, 1).Value
        currentNumber = myRow.Cells.Item(1, 2).Value
        Set bothcells = Range(myRow.Cells(1, 1), myRow.Cells(1, 2))
        With bothcells.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        result = Application.WorksheetFunction.Match(currentPrecinct, precinctArray, 0)
        If result <> "#N/A" Then
            precinctNumber = precinctArray(result, 5)
            If currentNumber < 1 Then
                myTitle = "Invalid number."
                myMsg = "BMD number " & currentNumber & " cannot be less than 1."
                response = MsgBox(myMsg, vbCritical, myTitle)
                With myRow.Cells(1, 2).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            ElseIf currentNumber > precinctNumber Then
            myTitle = "Invalid number."
                myMsg = "BMD number " & currentNumber & " cannot be greater than " & precinctNumber & "."
                response = MsgBox(myMsg, vbCritical, myTitle)
                With myRow.Cells(1, 2).Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End If
        Else
            myTitle = "Invalid precinct."
            myMsg = "Precinct " & currentPrecinct & " in BMD table was not found in Precinct table."
            response = MsgBox(myMsg, vbCritical, myTitle)
            With myRow.Cells(1, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 255
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    Next myRow
End Sub

I get this error at the Application.WorksheetFunction.Match statement.

What is the best way to validate one range against another?

 

[Fluff]

You're welcome & thanks for the feedback