Use .Cells.Find(What:=) to find values in Multiple Cells

All,

I am currently using the .Cells.Find method to put values into my userform. It does well finding one specific value in a cell but I can't figure out how to get this method to look at two values in two different cells. For example, I would like it to do the following:

If column B = sFind value and column C = sFind2 value, then pull the values from that row into the userform.

Can anyone please help? I've looked everywhere and can't seem to find the solution that I am looking for.

Thank you in advance for your help.

With dataSheet
            On Error Resume Next
            lRowFnd = .Cells.Find(What:=sFind, After:=Cells(1, 2), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlDown, MatchCase:=False, _
            SearchFormat:=False).Row

            'Required
            changeNum = .Cells(lRowFnd, 2)
            lineNum = .Cells(lRowFnd, 3)
            requestBy = .Cells(lRowFnd, 4)
            dateSub = .Cells(lRowFnd, 5)
            changeType = .Cells(lRowFnd, 6)
End With

Originally Posted by AlphaFrog

This link explains it well. See the "What if I want to search for multiple occurrences of an item" section

The ultimate search

Using information from the article that AlphaFrog referred to, we can create a much more efficient search loop than the one I initially suggested. The below set of code uses the FindNext method to loop through all instances of strFind in Column 2 and for each one tests whether the cell in Column 3 of that same row also contains strFind.

Dim strFirstAddress as String
Dim rngFind as Range
With dataSheet
    Set rngFind = .Cells(1, 2).EntireColumn.Find(strFind) is Nothing Then
    If Not rngFind is Nothing Then
        strFirstAddress = rngFind.Address
        Do Until rngFind is Nothing Or rngFind.Address = strFirstAddress
            If .Cells(rngFind.Row, 3).Value Like "*" & strFind & "*" Then
                lRowFnd = rngFind.Row
            End If
            Set rngFind = .Cells(1, 2).EntireColumn.FindNext(rngFind)
        Loop
    End If

'Required
    changeNum = .Cells(lRowFnd, 2)
    lineNum = .Cells(lRowFnd, 3)
    requestBy = .Cells(lRowFnd, 4)
    dateSub = .Cells(lRowFnd, 5)
    changeType = .Cells(lRowFnd, 6)
End With

Originally Posted by s2jrchoi

I just noticed that the code is looking for strFind in column 2 and in column 3. I think I should have explained my situation more clearly but what I am looking for is to look up strFind in column B and then in column C...

If I'm not mistaken Column 2 and Column 3 are the same columns as B and C.
If you need to find "JH051320140933" in Column B, and a certain integer in Column C, then use the same code as above except instead of looking for strFind in Column 3, you look for whatever integer you need. In the example below, I used the number 1.

Dim strFirstAddress as String
Dim rngFind as Range
strFind = "JH051320140933"   'Or however you're pulling these strings
With dataSheet
    Set rngFind = .Cells(1, 2).EntireColumn.Find(strFind) is Nothing Then
    If Not rngFind is Nothing Then
        strFirstAddress = rngFind.Address
        Do Until rngFind is Nothing Or rngFind.Address = strFirstAddress
            If .Cells(rngFind.Row, 3).Value = 1 Then    'Test if the value of the cell in Column C of this row is 1.
                lRowFnd = rngFind.Row
            End If
            Set rngFind = .Cells(1, 2).EntireColumn.FindNext(rngFind)
        Loop
    End If

'Required
    changeNum = .Cells(lRowFnd, 2)
    lineNum = .Cells(lRowFnd, 3)
    requestBy = .Cells(lRowFnd, 4)
    dateSub = .Cells(lRowFnd, 5)
    changeType = .Cells(lRowFnd, 6)
End With

If you need a certain value to be in two different cells in the same row, I would start at the top and move down one row at a time until you get to the row you need.

With dataSheet
    lRowFnd = 2
    Do Until lRowFnd > .Cells.SpecialCells(xlCellTypeLastCell).Row
        'Check if the value in lRowFind and Column 2 contains strFind and the value in lRowFind and Column 3 contains strFind.
        If .Cells(lRowFnd, 2).Value Like "*" & strFind & "*" And .Cells(lRowFnd, 3).Value Like "*" & strFind & "*" Then
            Exit Do
        End If
        lRowFnd = lRowFnd + 1
    Loop
'Required
    changeNum = .Cells(lRowFnd, 2)
    lineNum = .Cells(lRowFnd, 3)
    requestBy = .Cells(lRowFnd, 4)
    dateSub = .Cells(lRowFnd, 5)
    changeType = .Cells(lRowFnd, 6)
End With

This is just what I would try, but someone else might have a better suggestion.

Thank you so much AlphaFrog and MenacingBanjo! I will take a look and get back to you with my results. Thank you again!!

All,

Thank you for your help. I got it to work but I got the help from AlphaFrog's link because I did notice a few flaws in MB's code.

Option Explicit
Sub loadChanges()

Dim projName As String, dateSub As Variant, requestBy As String, changeNum As String, 
Dim changeType As String, changeReq As Variant, projDesc as variant, projName as variant, origCost as currency, lineItem As Integer, FindAddress As String, x As Long, sFind As Variant, lRowFnd As Variant

Dim dataSheet As Worksheet
Set dataSheet = ActiveWorkbook.Worksheets("Database")

sFind = requestForm.tbID.Value
   
With dataSheet
    Set R = .Cells(1, 2).EntireColumn.Find(sFind)
    If Not R Is Nothing Then
        FindAddress = R.Address
        x = 1
        Do
            lineItem = x
            lRowFnd = R.Row
            'Required
            changeNum = .Cells(lRowFnd, 2)

            requestBy = .Cells(lRowFnd, 4)
            dateSub = .Cells(lRowFnd, 5)
            changeType = .Cells(lRowFnd, 6)

            'Project Information
            changeReq = .Cells(lRowFnd, 7)
            projDesc = .Cells(lRowFnd, 9)
            projName = .Cells(lRowFnd, 10)
            origCost = .Cells(lRowFnd, 11)
            

            If sFind = changeNum And lineItem = 1 Then
                'Required
                requestForm.tbChangeNum.Value = changeNum
                requestForm.tbDate.Value = dateSub
                requestForm.cbChangeType.Value = changeType
                requestForm.tbUserName.Value = requestBy
                'Project Information
                requestForm.tbDesc.Value = projDesc
                requestForm.tbProjName.Value = projName
                requestForm.tbCost.Value = origCost
                requestForm.tbCost.Value = Application.WorksheetFunction.Dollar(requestForm.tbCost.Value, 2)
            ElseIf sFind = changeNum And lineItem = 2 Then
                requestForm.MultiPage2.Pages(1).Visible = True
                requestForm.tbProjDescSD.Value = projDesc
                requestForm.tbCostSD.Value = origCost
                requestForm.tbCostSD.Value = Application.WorksheetFunction.Dollar(requestForm.tbCostSD.Value, 2)
                requestForm.tbProjNameSD.Value = projName
                requestForm.tbChType1.Value = changeReq
            End If
            
            x = x + 1
            Set R = .Cells(1, 2).EntireColumn.FindNext(R)

        Loop While Not R Is Nothing And R.Address <> FindAddress
    End If
End With
            
Set R = Nothing

Thank you again!! I will mark this as solved.