using loop to populate data in textboxes and comboboxes after matching

[KalilMe]

hello
I have this code . it populates values in textboxes and comboboxes after matching with sheets in COLS A,B,C,D,E,F) so what I want using loop to make the code is short
this is the code

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Rng As Range
Dim Sel
Set ws = Sheets("first")
Sel = Me.ComboBox1.Value
If Sel <> "" Then
 Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
   ws.Cells(Rng.Row, "A") = Me.TextBox1.Value
  ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
  ws.Cells(Rng.Row, "C") = Me.TextBox3.Value
   ws.Cells(Rng.Row, "A") = Me.TextBox4Value
  ws.Cells(Rng.Row, "B") = Me.TextBox5Value
  ws.Cells(Rng.Row, "C") = Me.TextBox6Value
   ws.Cells(Rng.Row, "A") = Me.TextBox7Value
  ws.Cells(Rng.Row, "B") = Me.TextBox8Value
  ws.Cells(Rng.Row, "C") = Me.TextBox9.Value
  ws.Cells(Rng.Row, "D") = Me.ComboBox1.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox2.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX3.Value
    ws.Cells(Rng.Row, "D") = Me.ComboBox4.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox5.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX6.Value
 
 
 Else
 Me.TextBox1.Value = ""
 Me.TextBox2.Value = ""
 Me.TextBox3.Value = ""
 Me.TextBox4.Value ""
 Me.TextBox5.Value = ""
 Me.TextBox6.Value = ""
 Me.TextBox7.Value = ""
 Me.TextBox8.Value = ""
 Me.TextBox9.Value = ""
 Me.ComboBox1.Value = ""
 Me.ComboBox2.Value = ""
  Me.CoMBoboX3.Value = ""
  Me.ComboBox4.Value = ""
  Me.ComboBox5.Value = ""
   Me.CoMBoBoX6.Value = ""
 
 
 End If
End If
End Sub

 

[rlv01]

One way to reduce the code is to remove the lines that serve no purpose. I appears that most of the TextBox and ComboBox values do not matter because they are being written to cells that are then immediately overwritten by other TextBox and ComboBox values.

Sel = Me.ComboBox1.Value
If Sel <> "" Then
 Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
 If Not Rng Is Nothing Then
   ws.Cells(Rng.Row, "A") = Me.TextBox1.Value
  ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
  ws.Cells(Rng.Row, "C") = Me.TextBox3.Value
 
  ws.Cells(Rng.Row, "A") = Me.TextBox4Value
  ws.Cells(Rng.Row, "B") = Me.TextBox5Value
  ws.Cells(Rng.Row, "C") = Me.TextBox6Value

   ws.Cells(Rng.Row, "A") = Me.TextBox7Value
  ws.Cells(Rng.Row, "B") = Me.TextBox8Value
  ws.Cells(Rng.Row, "C") = Me.TextBox9.Value

  ws.Cells(Rng.Row, "D") = Me.ComboBox1.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox2.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX3.Value

    ws.Cells(Rng.Row, "D") = Me.ComboBox4.Value
   ws.Cells(Rng.Row, "E") = Me.ComboBox5.Value
    ws.Cells(Rng.Row, "F") = Me.CoMBoBoX6.Value

So in theory you could just delete those lines of code.

    Sel = Me.ComboBox1.Value
    If Sel <> "" Then
        Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)
        If Not Rng Is Nothing Then
             ws.Cells(Rng.Row, "A") = Me.TextBox7.Value
             ws.Cells(Rng.Row, "B") = Me.TextBox8.Value
             ws.Cells(Rng.Row, "C") = Me.TextBox9.Value

             ws.Cells(Rng.Row, "D") = Me.ComboBox4.Value
             ws.Cells(Rng.Row, "E") = Me.ComboBox5.Value
             ws.Cells(Rng.Row, "F") = Me.ComboBox6.Value

 

[DanteAmor]

But what you put in cells A, B and C

   ws.Cells(Rng.Row, "A") = Me.[B]TextBox1[/B].Value
  ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
  ws.Cells(Rng.Row, "C") = Me.TextBox3.Value

It is then eliminated by the following controls:

   ws.Cells(Rng.Row, "A") = Me.[B]TextBox4[/B]Value
  ws.Cells(Rng.Row, "B") = Me.TextBox5Value
  ws.Cells(Rng.Row, "C") = Me.TextBox6Value

But I show you a way to clear the controls:

    Else
      For i = 1 To 9
        Me.Controls("TextBox" & i).Value = ""
      Next
      '
      For i = 1 To 6
        Me.Controls("ComboBox" & i).Value = ""
      Next
    End If

 

[My Aswer Is This]

Another way to clear all those controls is to Unload the entire Userform if that is what your using.

 

[KalilMe]

Else
For i = 1 To 9
Me.Controls("TextBox" & i).Value = ""
Next
'
For i = 1 To 6
Me.Controls("ComboBox" & i).Value = ""
Next
End If

@DanteAmor how implement your idea in my code . actually I want use loop to copy the sheet so , but I have no enough knowladge in vba . may you guide me please?

 

[DanteAmor]

actually I want use loop to copy the sheet so , but I have no enough knowladge in vba . may you guide me please?

But your code is not clear, you are overlapping values in the same cells.
Assuming that you are going to put values from column A to O, one column for each control, then it could be like this:

The following is an example.
Try to adapt it to your need. If you have problems, then come back here and explain in your words which control goes in which column.
You can also use the XL2BB tool minisheet to show what goes on your sheet.

Private Sub CommandButton1_Click()
  Dim ws As Worksheet
  Dim Rng As Range
  Dim Sel
  Dim i As Long
  
  Set ws = Sheets("first")
  Sel = Me.ComboBox1.Value
  If Sel <> "" Then
    Set Rng = ws.Columns(10).Find(Sel, lookat:=xlWhole)
    If Not Rng Is Nothing Then
      For i = 1 To 9
        ws.Cells(Rng.Row, i) = Me.Controls("TextBox" & i).Value
      Next
      
      For i = 1 To 6
        ws.Cells(Rng.Row, i + 9) = Me.Controls("ComboBox" & i).Value
      Next
    Else
      For i = 1 To 9
        Me.Controls("TextBox" & i).Value = ""
      Next
      '
      For i = 1 To 6
        Me.Controls("ComboBox" & i).Value = ""
      Next
    End If
  End If
End Sub

 

[KalilMe]

@DanteAmor actually it doesn't show any thing . it doesn't seem I can deal with the code .so the textboxes linked with col A,B,C and the comboboxes link with COL D,E,F
the combobox 10 is search I put the item based on COL A . the search based on INV.NO
see the picture

 

[rlv01]

but I have no enough knowladge in vba . may you guide me please?

With respect to the code you posted, did you write it yourself, or is it code you obtained from others?

 

[KalilMe]

is it code you obtained from others?

yes. is it problem?

 

[DanteAmor]

Now I have more questions than answers.

1. In your macro you look in column 4.
But now the values are in column 1?

Set Rng = ws.Columns(4).Find(Sel, lookat:=xlWhole)

2. I guess you must have a reason to save 3 records, why 3 records?

3. Is it always 3 records?

4. So it's 9 textbox1 and 9 combobox?

5. If the value of combobox1 is equal to the value of textboxes 1, 4 and 7, then it is not necessary to capture the values in textboxes 1, 4 and 7, is it?

6. If this is how you need it, that is, 3 records, maybe there is not much to simplify, only after passing the first 6 values, you should increase the row by 1.
Then the cycle would only apply in the way of cleaning the controls.

Comment and I help you with the final code to pass the values to the sheet.