KentBurel
Board Regular
- Joined
- Mar 27, 2020
- Messages
- 68
- Office Version
- 2019
- Platform
- Windows
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:
I get this error at the Application.WorksheetFunction.Match statement.
What is the best way to validate one range against another?
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:
VBA Code:
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?