multiple find functions and if same row then

[SCOTTWHITTAKER2333]

What would be the best way to use the find function (or maybe not with the find function if you have a better way) in VBA to find first a value = to a textbox1 in range(G3:G5) and find the value of textbox2 in range(H3:H5) on sheet1 and the IF and only if they are in the same row (i.e. Cell G3 and H3) then select cell AG( and that row number ) and place the value of textbox3 in it.

 

[Ralajer]

Try the below

This function is passed two ranges to search within and two string to search for and returns the row number if they match and -1 otherwise. If duplicate values exist this may or may not work as it stops after it found the first of each value.

Private Function findMatchRow(val1 As String, val2 As String, _
         rng1 As Range, rng2 As Range) As Long
    
    Dim foundRow1 As Long
    Dim foundRow2 As Long
    
    '// Default values to be used to check if values were found
    foundRow1 = -1
    foundRow2 = -2
    
    '// Find errors if nothing can be found therefore,
    '//  resume next leaving the found row as default
    On Error Resume Next
    foundRow1 = rng1.Find(What:=val1, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                Searchorder:=xlByRows).Row
    
    foundRow2 = rng2.Find(What:=val2, _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                Searchorder:=xlByRows).Row
    
    On Error GoTo 0     '// Resume normal error handling
    
    '// Rows don't match
    If foundRow1 <> foundRow2 Then
        '// Return value indicates that values not in same row
        '//  or were not found
        findMatchRow = -1
    Else
        '// Both values were found and are on the same row
        '//  Return either found row
        findMatchRow = foundRow1
    End If
    
End Function

This is test code that show how to use the function in your situation.

Sub test()
    Dim rngTxt1 As Range
    Dim rngTxt2 As Range
    Dim valTxt1 As String
    Dim valTxt2 As String
    
    Dim sameRow As Long
    
    Set rngTxt1 = Range("G3:G5")
    Set rngTxt2 = Range("H3:H5")
    
    valTxt1 = textbox1.Text
    valTxt2 = textbox2.Text
        
    sameRow = findMatchRow(valTxt1, valTxt2, rngTxt1, rngTxt2)
    '// -1 is the rows do not match or values not found return value
    If sameRow <> -1 Then
        textbox3.Text = Range("AG" & sameRow).Value
    End If

End Sub

 

[SCOTTWHITTAKER2333]

Seems pretty close.
I am geting a Compile error: Can't find project or libraryon this part

sameRow = [B][U]findMatchRow[/U][/B](valTxt1, valTxt2, rngTxt1, rngTxt2)

Possibly has something to do with the fact that I have to for some reason put in the words VBA. before almost any normal function that I use. Not really sure why I have to do that as I never used to then all the sudden I had to one day. Example If I use the Format() function in VBA then I have to use

VBA.Format()

But that probably won't work for UDF functions.

 

[SCOTTWHITTAKER2333]

Ok I unchecked the missing refrence and now I get a sub or function not defined.

 

[Ralajer]

Where did you place the findMatchRow function in your code. It should be in the same module as the rest of your code or you have to get rid of the Private.

Let me know.

 

[SCOTTWHITTAKER2333]

Ya, I got past the errors by changing it to a public function but it doesn;t seem to be working. I have sience renamed some things so I will post the code as I have it now.

This is the part that we are working with:

If Me.TextBox26.Value <> "" Then
    
    
    Set rngTxt1 = Range("G3:G5")
    Set rngTxt2 = Range("H3:H5")
    
    valTxt1 = Me.ComboBox2.Value
    valTxt2 = Me.ComboBox1.Value
        
    sameRow = findMatchRow(valTxt1, valTxt2, rngTxt1, rngTxt2)
    '// -1 is the rows do not match or values not found return value
    If sameRow <> -1 Then
        Range("AJ" & sameRow).Select
        ActiveCell = Me.TextBox26.Text
        ActiveCell.Offset(0, -1).Select
        ActiveCell = Me.ComboBox3.Value
        ActiveCell.Offset(0, -1).Select
        ActiveCell = Format(Now(), "HH:MM")
    End If

And this is the full code there are a lot of other things happening so maybe some things not working well together:

Private Sub CommandButton1_Click()
Dim area1 As Variant
 Dim area2 As Variant
    Dim i As Long
    Dim i2 As Long
    Dim rngTxt1 As Range
    Dim rngTxt2 As Range
    Dim valTxt1 As String
    Dim valTxt2 As String
    
    Dim sameRow As Long
    
    
If Me.ComboBox2.Value = "" Then
MsgBox "Please choose a shift.", vbExclamation, "s"
Me.ComboBox2.SetFocus
Exit Sub
End If
 If Me.ComboBox1.Value = "" Then
MsgBox "Please choose a SKU.", vbExclamation, "s"
Me.ComboBox1.SetFocus
Exit Sub
End If
    
    Range("C12").Select
area1 = Array("C12", "D12", "F12", "H12", "J12", "L12", "N12", "P12", "R12", "T12", "V12", "X12", "C27", "D27", "F27", "H27", "J27", "L27", "N27", "P27", "R27", "T27", "V27", "X27")
   On Error Resume Next
       
    For i = LBound(area1) To UBound(area1)
         
           If ActiveCell.Value = "" Then Exit For
            If i = UBound(area1) Then
                
                MsgBox "The sheet is full please start a new page"
                Exit Sub
            Else
                 
                Range(area1(i + 1)).Select
            End If
        
    Next i

ActiveCell = Me.ComboBox1.Value
ActiveCell.Offset(1, 0).Select
ActiveCell = Me.ComboBox2.Value
ActiveCell.Offset(1, 0).Select
ActiveCell = VBA.Format(Now(), "HH:MM")
ActiveCell.Offset(1, 0).Select
ActiveCell = ((Me.TextBox2.Value - Me.TextBox18.Value) - Me.TextBox1.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = ((Me.TextBox3.Value - Me.TextBox18.Value) - Me.TextBox1.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = ((Me.TextBox4.Value - Me.TextBox18.Value) - Me.TextBox1.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = ((Me.TextBox5.Value - Me.TextBox18.Value) - Me.TextBox1.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = ((Me.TextBox6.Value - Me.TextBox18.Value) - Me.TextBox1.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = ((Me.TextBox2.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox3.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox4.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox5.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox6.Value - Me.TextBox18.Value) - Me.TextBox1.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = (((Me.TextBox2.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox3.Value - TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox4.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox5.Value - Me.TextBox18.Value) - Me.TextBox1.Value) + ((Me.TextBox6.Value - Me.TextBox18.Value) - Me.TextBox1.Value)) / Application.Count(Me.TextBox2.Value, Me.TextBox3.Value, Me.TextBox4.Value, Me.TextBox5.Value, Me.TextBox6.Value)
ActiveCell.Offset(1, 0).Select
ActiveCell = Me.TextBox1.Value
ActiveCell.Offset(1, 0).Select
ActiveCell = Me.TextBox9.Value
ActiveCell.Offset(1, 0).Select
ActiveCell = Me.TextBox8.Value
ActiveCell.Offset(-14, 0).Select
ActiveCell = Me.TextBox25.Value
Me.TextBox2.Value = ""
Me.TextBox10.Value = ""
Me.TextBox3.Value = ""
Me.TextBox11.Value = ""
Me.TextBox4.Value = ""
Me.TextBox12.Value = ""
Me.TextBox5.Value = ""
Me.TextBox13.Value = ""
Me.TextBox6.Value = ""
Me.TextBox14.Value = ""
Me.TextBox8.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.TextBox21.Value = ""
Me.TextBox22.Value = ""
Me.TextBox23.Value = ""
Me.TextBox24.Value = ""
Me.TextBox25.Value = ""
ActiveCell.Offset(2, 1).Select
Me.TextBox8.SetFocus
If Me.TextBox26.Value <> "" Then
    
    
    Set rngTxt1 = Range("G3:G5")
    Set rngTxt2 = Range("H3:H5")
    
    valTxt1 = Me.ComboBox2.Value
    valTxt2 = Me.ComboBox1.Value
        
    sameRow = findMatchRow(valTxt1, valTxt2, rngTxt1, rngTxt2)
    '// -1 is the rows do not match or values not found return value
    If sameRow <> -1 Then
        Range("AJ" & sameRow).Select
        ActiveCell = Me.TextBox26.Text
        ActiveCell.Offset(0, -1).Select
        ActiveCell = Me.ComboBox3.Value
        ActiveCell.Offset(0, -1).Select
        ActiveCell = Format(Now(), "HH:MM")
    End If

Me.TextBox26.Value = ""
Me.ComboBox3.Value = ""
Me.TextBox8.SetFocus
End If
ActiveWorkbook.Save
End Sub

By the way this code is attached to a button on a userform if that makes a difference

 

[Ralajer]

Nothing jumps at me as being wrong but I can't see what other than code you are working with.

When you say it not working what is it not setting the values in textbox26 or is it something else.

• What are these values in ComboBox2.Value and ComboBox1.Value
• What are the values in the ranges
• Is everything on the referenced in the code on the same sheet

Also add the red line in the below and when you run it let me know what the msgbox says.

    Set rngTxt1 = Range("G3:G5")
    Set rngTxt2 = Range("H3:H5")
    
    valTxt1 = Me.ComboBox2.Value
    valTxt2 = Me.ComboBox1.Value

    sameRow = findMatchRow(valTxt1, valTxt2, rngTxt1, rngTxt2)

    [COLOR="Red"]msgbox "The Find Match Row # is: " & sameRow[/COLOR]

    '// -1 is the rows do not match or values not found return value
    If sameRow <> -1 Then
        Range("AJ" & sameRow).Select
        ActiveCell = Me.TextBox26.Text
        ActiveCell.Offset(0, -1).Select
        ActiveCell = Me.ComboBox3.Value
        ActiveCell.Offset(0, -1).Select
        ActiveCell = Format(Now(), "HH:MM")
    End If

 

[SCOTTWHITTAKER2333]

The message box came out as -1.
I'm not really sure why it is though.
The ranges are filled out by a combobox on a different userform when the sheet is opened this userform is called.

Then then later when the userform that we are working on is used its combo boxes use the same row source's as the above mentioned one so I know the data is the same. I even checked the cell formating to make sure it was correct.

Combobox 1 and range h3:h5 are product numbers

Combobox2 and range g3:h5 are a possible "1st" "2nd" or "3rd" value.

and... wow I just figured it out as I was typing this message. I just realized that column h is merged with colomn I in those cells. When I unmerged them it worked fine. Sorry about that, I feel like an Idiot.

Thank you so much for your help.