Using .Find with Multiple Criteria (similiar to Match function)

[natekris8183]

All right, sort of a strange one here. I have finished putting together a cash order sheet (I work at a small credit union). While everything functions fine, the only complication is if someone needs to resubmit (or correct) an existing order. I've figured how to incorporate the Countifs VBA to search the associated columns for the matching data and obviously if it returns a value greater than 1 than the order already exists, BUT now it's navigating to the correct cell to replace the data (or copy/paste).

My gut tells me (when looking through some other forums, which I'll post an associated one below) is to use the .Find method but I don't see the ability to identify the multiple criteria (unless I attempt to do it using a concatenated set of variables, but I am not sure I'd know how to make them concatenate correctly for syntax purposes. Here's the scenario (and the layout is the same but far fewer columns for simplicity):

Column(A): Date (Matching Criteria 1)
Column(B): Branch (Matching Criteria 2)
Column(C): Currency (associated data)
Column(D): Coin (associated data)
Column(E): Total (associated data)​

So I need to match the date AND branch, because they're multiple locations submitting orders for the same day. Currently on the order sheet I have a matching function as the following where it matches the date AND branch and then locates the previous order information by denomination

{=IF(COUNTIF(tblAllBranchData[Branch1],$D$4)>0,INDEX(tblAllBranchData,MATCH(MAX(IF(tblAllBranchData[Branch1]=$D$4,tblAllBranchData[Date]))&$D$4,tblAllBranchData[Date]&tblAllBranchData[Branch1],0),5),0)}

The concept would be the same but the vba .Find method would locate the cell in Column(A) with the same branch name (both variably set earlier through some other expressions) so that the copy/paste expression would copy over the existing data. thanks ahead to anyone that may be able to help!

This is the .Find forum where I think it is I need to start.
http://www.vbforums.com/showthread.php?634644-Excel-Find-Method-in-Excel-VBA-(Any-version-of-Excel)

 

[natekris8183]

If this helps at all, this is the vba to search if the date and branch already exist (again this is a dummy worksheet, but the layout is the same so I just have to change ranges or variables). So IF the res1 is greater than 1, then when I go to copy/paste the data to the storage worksheet, it finds the same reference cell is counted in the Countif.

Sub WorkSheet_Activate()
Dim msgQues, msgAns, TheDeliveryDate, Branch, res1 As String
Dim TheDate As Date
Dim xRng1, xRng2 As Range

Set xRng1 = Range("Table1[Date]")
Set xRng2 = Range("Table1[Branch]")
Branch = Application.InputBox("Input Branch")
TheDeliveryDate = Application.InputBox("Input Delivery Date")
If IsDate(TheDeliveryDate) Then
    TheDate = DateValue(TheDeliveryDate)
Else
    MsgBox "Invalid date"
End If
res1 = Application.CountIfs(xRng1, TheDeliveryDate, xRng2, Branch)
If res1 > 0 Then
    msgQues = "Order previously placed for this date. Continue with new order to replace existing?"
    msgAns = MsgBox(msgQues, vbYesNo)
    If msgAns = vbYes Then
        MsgBox "Thank You. Please continue!"
    Else
        MsgBox "Order Cancelled"
    End If
Else
    MsgBox "Thank You. Please Continue 2!"
End If
End Sub

 

[natekris8183]

At this point I am thinking of a loop whereby I search by "Table1[Date]" for the date input from the InputBox and then state that if the cell to the right of that date is equal to the Branch name input in the InputBox is equal to, but I need the loop the end after cycling through all dates matching the date input since obviously most often it will NOT match a branch name corresponding to that date. The only problem is if I use the .Find method I need to reset the the next loop to start from the previous incorrect match. It feels like somehow utlizing a Match statement would be infinitely easier, but I am just not sure.

 

[natekris8183]

I'm getting so close I can almost TASTE it. So I've gotten the find to properly LOCATE the information (unfortunately it is a loop and could get a little timely as more and more data is input, but for now it's the only solution I've come up with). The second part is then to select the row in Column(A) corresponding to the associated data. Here's the first section:

Sub MatchCell()
Dim c As Range, Branch As String, TheDeliveryDate As String
Dim TheDate As Date
Branch = Application.InputBox("Input Branch")
TheDeliveryDate = Application.InputBox("Input Delivery Date")
If IsDate(TheDeliveryDate) Then
    TheDate = DateValue(TheDeliveryDate)
Else
    MsgBox "Invalid date"
End If
For Each c In Range("Table1[Date]")
If c.Value = TheDeliveryDate And c.Offset(0, 1) = Branch Then
MsgBox "An order exists for this date. Resubmit new order for this date?", vbYesNo
End If
Next
End Sub

Any thoughts on how to "select" the first cell in the row it's finding the data in?

 

[natekris8183]

OK... so I have it so that it checks to see if the statement is true or not to indicate the duplicae data. I am still stuck on the locating the associated row in the table. The cheat way I came up with was using a dummy cell in excel with the match formula and concatenanted criteria to produce the associate Row (however I have tried this in VBA and I keep getting a type MisMatch error). I, obviously, would prefer doing this IN VBA instead of a dummy cell, so anyone with some thoughts I'd appreciate it.

 

[Norie]

You might want to look at using SUMPRODUCT to locate based on more than one criteria.

Sub MatchCell()
Dim c As Range, Branch As String, TheDeliveryDate As String
Dim TheDate As Date
Dim rw As Variant
Dim Resp As VbMsgBoxResult

    Branch = Application.InputBox("Input Branch")
    
    TheDeliveryDate = Application.InputBox("Input Delivery Date")
    
    If IsDate(TheDeliveryDate) Then
        TheDate = DateValue(TheDeliveryDate)
    Else
        MsgBox "Invalid date"
    End If

    rw = Evaluate("SUMPRODUCT(ROW(Table1[Date]),--(Table1[Date]=" & CDbl(TheDate) & "), --(Table1[Branch]=""" & Branch & """))")


    If Not IsError(rw) Then
        Resp = MsgBox("An order exists for this date. Resubmit new order for this date?", vbYesNo)
    End If

End Sub