search in worksheet for multiple words via userform

[Max01]

Hi,

I have created a database, where data from the userform is entered into the Worksheet ("data_entry") into the respective column.
Now I want to load counter readings (in a production of components) automatically into the Userform.
As soon as the respective component is selected via the "Combobox5", and then the Side (in this case left / right) is selected,
the last counter reading of the respective Side of the Component is entered in the "Textbox19".

I have put together something that works roughly, but it only searches for the name of the component (Combobox5), not the respective Side (Combobox8).
This means that it only works according to the criterion "Component 1".

I have already looked in several forums for similar problems, but have not found anything similar.
I would be very grateful if someone can help me.

Here is the Code:

'Component 1
'load the last Counter 'End'-Entry for Component 1
If ComboBox5.Text = "Component 1" and Combobox8.Text = "Left" Then
Dim cpnt1 As Range
Dim side_left As Range
Set cpnt1 = Worksheets("data_entry").Range("F:M").Find(what:="Component 1", LookAt:=xlWhole, SearchDirection:=xlPrevious) 'search for "Component1" in the last line of sheets "data_entry"
Set side_left = Worksheets("data_entry").Range("F:M").Find(what:="Left", LookAt:=xlWhole, SearchDirection:=xlPrevious) 'search for "Left" in the last line of sheets "data_entry"
With Sheets("data_entry")
    On Error GoTo nofound_cpnt1: 'in case no begin or end was found, goto "nofound_cpnt1:"
    If cpnt1.Cells.Offset(0, 6).Value = "" Then 'if End is empty
TextBox19.Text = cpnt1.Offset(0, 5).Value 'take counter begin, if available
    Else 'Else = if cell is not empty
TextBox19.Text = cpnt1.Offset(0, 6).Value 'take counter end, if available
    End If
End With
nofound_cpnt1: 'let "Textbox19" empty
'do nothing
End If

 

[DanteAmor]

Hi @Max01 . Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Could you provide a sample of your data, of what you have in the F:M columns and with some color highlight an example, of what you select in combobox5, of what you select in combobox8 and what you expect as a result in the textbox19.

It would be nice if you could provide 8 or 10 examples, to understand what result you expect.
If you only put an example record it is difficult to understand this:

the last counter reading of the respective Side of the Component is entered in the "Textbox19".

Note XL2BB:
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.


--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------

 

[Max01]

Dear Dante,

Thank you for the kind words!
Due to the fact that the actual file has too much code and the userform has much more content, I have changed it a bit.
And excuse me for possible language mistakes, english is not my native language

This is what the worksheet looks like.
By that I have changed the document a little bit to make it more understandable,
the range "F:M" in my code is now "D:I" - the range from the system/attachment to counter end.
Important are the boxes marked in red.

This is what the userform looks like in which you can add the new entry. It opens in front of the worksheet.

Once you have selected the system / attachment, the combobox5 shows which Project you can run there as a dropbox.
Once you have selected the project, you can select the side of the project. (left or right)
After you have done this, the last counter reading is automatically loaded into the userform in Textbox19. The counter end Textbox can be neglected.

If no End counter is found or not entered, the Start counter is used instead.
If this is also not entered, the text box19 remains empty.

This is roughly how it is supposed to work:

I hope it is more understandable by the pictures and you can do something with it.

Thanks a lot!
Max

 

[DanteAmor]

Try this:

Private Sub ComboBox8_Change()
  Dim sh As Worksheet
  Dim i As Long
  
  TextBox19.Value = ""
  If ComboBox8.ListIndex = -1 Then Exit Sub
  If ComboBox5.ListIndex = -1 Then Exit Sub
  
  Set sh = Sheets("data_entry")
  
  For i = sh.Range("E" & Rows.Count).End(3).Row To 3 Step -1
    If sh.Range("E" & i).Value = ComboBox5.Value And _
       sh.Range("F" & i).Value = ComboBox8.Value Then
       
      If sh.Range("I" & i).Value <> "" Then
        TextBox19.Value = sh.Range("I" & i).Value
      Else
        TextBox19.Value = sh.Range("H" & i).Value
      End If
      
      Exit For
    End If
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

 

[Max01]

Try this:

Private Sub ComboBox8_Change()
  Dim sh As Worksheet
  Dim i As Long
 
  TextBox19.Value = ""
  If ComboBox8.ListIndex = -1 Then Exit Sub
  If ComboBox5.ListIndex = -1 Then Exit Sub
 
  Set sh = Sheets("data_entry")
 
  For i = sh.Range("E" & Rows.Count).End(3).Row To 3 Step -1
    If sh.Range("E" & i).Value = ComboBox5.Value And _
       sh.Range("F" & i).Value = ComboBox8.Value Then
      
      If sh.Range("I" & i).Value <> "" Then
        TextBox19.Value = sh.Range("I" & i).Value
      Else
        TextBox19.Value = sh.Range("H" & i).Value
      End If
     
      Exit For
    End If
  Next
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------

It works perfectly!
Thank you so much, also, that you have taken the time for it..

currently it takes the data of the last line, would it be possible that it enters the last found entry of the worksheet (counter begin / end)?
if not, I am still grateful to them, have a nice day!

Max

 

[DanteAmor]

would it be possible that it enters the last found entry of the worksheet (counter begin / end)?

Explain that with several examples.
Give several examples of what you have and what you expect from results. Don't limit yourself to one example, the clearer your examples are, the easier it will be for me to understand what you want.

 

[Max01]

Explain that with several examples.
Give several examples of what you have and what you expect from results. Don't limit yourself to one example, the clearer your examples are, the easier it will be for me to understand what you want.

with the above code of you it loads the LAST row as soon as you select the corresponding project (Combobox5) with the respective Side (Combobox8) using the 2 Comboboxes.

If the row in the worksheet is empty, but the one above it (or several lines above), which matches the Combobox content, is filled in (no matter if counter begin / end), it would still take the last one.
Would it be possible to build in a kind of "search" function, in which, for example, if "Project 1" is selected in Combobox5 and the Side "Left" is selected in Combobox8, it takes the last FILLED line of the Side?
However, if no begin or end counter is found, for example, if the worksheet is empty, the "Textbox19" remains empty.

Thank you!
Max