Ensure Values In One Range Matches Values In Another

[majinvegito123]

Hi guys. I'm trying to make it so I can ensure that a particular set of values that are user-entered MATCH a list that I have off to the right or else the code is supposed to error out and send a message saying it's invalid and try again.

I have a spot for four entries. They are labelled BuyOne, BuyTwo, BuyThree, BuyFour and I need them to match a list that is generated via SQL in Column O (This list can change at any time either smaller or larger, so it's variable). The list in Column O is labelled as "Query_Buyer"

I need this done via VBA but I can't quite figure out how to properly do the check. Here's what I've got so far. Keep in mind that it doesn't work and I get a type mismatch.

If Range("BuyOne") = vbNullString Or Range("BuyOne") = Range("O1:O50") Then
    MsgBox "Invalid Buyer Code.."
    Range("BuyTwo").Select
Else: End If

If Range("BuyTwo") = vbNullString Or Range("BuyTwo") = Range("O1:O50") Then
    MsgBox "Invalid Buyer Code.."
    Range("BuyTwo").Select
Else: End If

If Range("BuyThree") = vbNullString Or Range("BuyThree") <> Range("O1:O50") Then
    MsgBox "Invalid Buyer Code.."
    Range("BuyThree").Select
Else: End If

If Range("BuyFour") = vbNullString Or Range("BuyFour") <> Range("O1:O50") Then
    MsgBox "Invalid Buyer Code.."
    Range("BuyFour").Select
Else: End If

Can anyone help me figure out what's going on?

 

[majinvegito123]

Bump? Getting a headache figuring this one out

 

[Alex Blakenburg]

See if this gives you any ideas.
I am assuming BuyOne, BuyTwo etc are range names.

Sub TestValidate()

    Dim chkFind As Boolean
    Dim lastRow As Long
    Dim rBuyerList As Range
    Dim arrBuyer As Variant
    Dim i As Long
    
    lastRow = Range("O" & Rows.Count).End(xlUp).Row
    Set rBuyerList = Range("O1:O" & lastRow)
    arrBuyer = Array("BuyOne", "BuyTwo", "BuyThree", "BuyFour")
    
    For i = 0 To UBound(arrBuyer)
        With Application
            chkFind = .IfError(.Match(Range(arrBuyer(i)), Range("O1:O50"), 0), 0)
        End With
        
        If Range(arrBuyer(i)) = vbNullString Or chkFind = False Then
            MsgBox "Invalid Buyer Code.." & arrBuyer(i)
            Range(arrBuyer(i)).Select
        End If
    Next i
 
End Sub

 

[majinvegito123]

See if this gives you any ideas.
I am assuming BuyOne, BuyTwo etc are range names.

Sub TestValidate()

    Dim chkFind As Boolean
    Dim lastRow As Long
    Dim rBuyerList As Range
    Dim arrBuyer As Variant
    Dim i As Long
   
    lastRow = Range("O" & Rows.Count).End(xlUp).Row
    Set rBuyerList = Range("O1:O" & lastRow)
    arrBuyer = Array("BuyOne", "BuyTwo", "BuyThree", "BuyFour")
   
    For i = 0 To UBound(arrBuyer)
        With Application
            chkFind = .IfError(.Match(Range(arrBuyer(i)), Range("O1:O50"), 0), 0)
        End With
       
        If Range(arrBuyer(i)) = vbNullString Or chkFind = False Then
            MsgBox "Invalid Buyer Code.." & arrBuyer(i)
            Range(arrBuyer(i)).Select
        End If
    Next i
 
End Sub

This is exactly the kind of code validation I was looking for. IT works perfectly and does what I need it to do. Thank you for your help!

 

[majinvegito123]

Not sure how to edit posts but I do have one more question. How do I get it so where a blank entry is acceptable?

 

[majinvegito123]

See if this gives you any ideas.
I am assuming BuyOne, BuyTwo etc are range names.

Sub TestValidate()

    Dim chkFind As Boolean
    Dim lastRow As Long
    Dim rBuyerList As Range
    Dim arrBuyer As Variant
    Dim i As Long
   
    lastRow = Range("O" & Rows.Count).End(xlUp).Row
    Set rBuyerList = Range("O1:O" & lastRow)
    arrBuyer = Array("BuyOne", "BuyTwo", "BuyThree", "BuyFour")
   
    For i = 0 To UBound(arrBuyer)
        With Application
            chkFind = .IfError(.Match(Range(arrBuyer(i)), Range("O1:O50"), 0), 0)
        End With
       
        If Range(arrBuyer(i)) = vbNullString Or chkFind = False Then
            MsgBox "Invalid Buyer Code.." & arrBuyer(i)
            Range(arrBuyer(i)).Select
        End If
    Next i
 
End Sub

Another thing I was trying to figure out as well.. How do I get the results of the array to pass into their specific variables? IE BuyOne BuyTwo BuyThree. I have a Sub immediately after which makes use of those variables.

 

[majinvegito123]

I got the program to work for the most part, but I think I'm misunderstanding how these things work though because in the runFinished subroutine, it's putting in "BuyOne" etc in the e.Buyer section when I put in arrBuyer(0) where I really need it to output what BuyOne CONTAINS (which is actually a value like CSW) How do I go about doing that?

If Range("reqFrDt") = vbNullString Or IsDate(Range("reqFrDt")) = False Then
    MsgBox "From date missing..."
    Range("reqFrDt").Select
    End
Else
    sFrDt = Range("reqFrDt")
End If

If Range("reqToDt") = vbNullString Or IsDate(Range("reqToDt")) = False Then
    MsgBox "To date missing or invalid.."
    Range("reqToDt").Select
    End
Else
    sToDt = Range("reqToDt")
End If

    
    lastRow = Range("O" & Rows.Count).End(xlUp).Row
    Set rBuyerList = Range("O1:O" & lastRow)
    arrBuyer = Array("BuyOne", "BuyTwo", "BuyThree", "BuyFour")
    
    For i = 0 To UBound(arrBuyer)
        With Application
            chkFind = .IfError(.Match(Range(arrBuyer(i)), Range("O1:O50"), 0), 0)
        End With
        
        If Range(arrBuyer(i)) = vbNullString Or chkFind = False Then
            MsgBox "Invalid Buyer Code.." & arrBuyer(i)
            Range(arrBuyer(i)).Select
        End If
    Next i

Call runFinished(sFrDt, sToDt, arrBuyer)

Sheets("Main Sheet").Select

MsgBox ("done...")


End Sub

Sub runFinished(sFrDt As String, sToDt As String, arrBuyer As Variant)
Dim SQL As String

' add a new work sheet
ActiveWorkbook.Worksheets.Add

' dispay Criteria
Cells(1, 1) = "Run Date: " & Now()
Call MergeLeft("A1:B1")

Cells(2, 1) = "Criteria:"
Cells(2, 2) = "From " & Range("reqFrDT") & " -To- " & Range("reqToDt")


' SQL
SQL = "select a.StockCode [Finished Part], a.QtyToMake, FQOH,FQOO,/*FQIT,*/FQOA,  b.Component [Base Material], CQOH,CQOO,CQIT,CQOA " & _
"from ( " & _
"    SELECT StockCode, sum(QtyToMake) QtyToMake " & _
"    from [MrpSugJobMaster] " & _
"    WHERE 1 = 1 " & _
"    AND JobStartDate >= '" & sFrDt & "' " & _
"    AND JobStartDate <= '" & sToDt & "' " & _
"    AND JobClassification = 'OUTS' " & _
"    AND ReqPlnFlag <> 'I'  AND Source <> 'E' Group BY StockCode " & _
"    ) a " & _
"LEFT JOIN BomStructure b on a.StockCode = b.ParentPart " & _
"LEFT JOIN ( " & _
"            select StockCode, sum(QtyOnHand) FQOH, Sum(QtyAllocated) FQOO, Sum(QtyInTransit) FQIT, Sum(QtyOnOrder) FQOA " & _
"            from InvWarehouse " & _
"            where Warehouse in ('01','DS','RM') " & _
"            group by StockCode " & _
") c on a.StockCode = c.StockCode " & _
"LEFT JOIN ( " & _
"            select StockCode, sum(QtyOnHand) CQOH, Sum(QtyAllocated) CQOO, Sum(QtyInTransit) CQIT, Sum(QtyOnOrder) CQOA " & _
"            from InvWarehouse " & _
"            where Warehouse in ('01','DS','RM') " & _
"            group by StockCode " & _
") d on b.Component = d.StockCode "
SQL = SQL & _
"LEFT JOIN InvMaster e on a.StockCode = e.StockCode " & _
"WHERE 1 = 1 " & _
"and e.Buyer in  ('" & BuyOne & "','" & BuyTwo & "','" & arrBuyer(2) & "','" & arrBuyer(3) & "') " & _
"ORDER BY a.StockCode "