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 should do it like

result = Application.Match(currentPrecinct, precinctArray, 0)
        If Not IsError(result) Then

 

[KentBurel]

The result from the Application.Match call is set to "Error 2042". What does that mean?

Notice I'm using Match to lookup a value in an array. I see conflicting information about the function. Can it reference an array? I see examples where it references a range. Which would be better? I thought loading the Precinct master table into an array would be faster.

 

[Fluff]

It means there was no match.
What is the range of

Range("Precincts")

 

[KentBurel]

 

[KentBurel]

There are 29 rows in all.

 

[Fluff]

The match function can only take a 1D array or range, try

precinctArray = Range("Precincts").Columns(1).value

 

[KentBurel]

It's working now. I don't understand how I missed that the match could only take a one dimensional array. You see that I need the number of BMDs allowed in the precinct so I need the fifth column of that array as well. I wound up defining 2 arrays. The first is precinctArray1D using the code you supplied and the original 2 dimensional array called precinct Array. I uncommented the Redim statement to make the precinctArray 2 dimensional. I got it to work.

One of my rules to live by is "Working code is more efficient than non-working code." However, can you see any way to make this working code more efficient?

I'm still a rookie at this. With your help, I'm gradually making it work. I really appreciate all your time and expertise. Thank you.

 

[Fluff]

I can't really help make the code more efficient, as I don't really know what it's doing.

 

[KentBurel]

I understand. Thanks.