add anew row whan fill data from userform to sheet

[abdelfattah]

hello
[FONT=&quot]i have userform contains combobox1,2,3 and textbox1,2,3 the sheets contains 6 columns a,b,c,d,e,f i would code to add data from userform to the next row in sheet1 the combobox 1,2,3 linked with range from a20:c20 and textbox1,2,3 linked with ranges from d20:f20 every time i fill data in combobox and textbox it moves the next row without left empty row in the end how can i do it by code vba ,please?[/FONT]

 

[DanteAmor]

Try this

Private Sub CommandButton1_Click()
  If ComboBox1 = "" Then
    MsgBox "Fill combo1"
    ComboBox1.SetFocus
    Exit Sub
  End If
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row + 1
  Range("A" & lr).Value = ComboBox1
  Range("B" & lr).Value = ComboBox2
  Range("C" & lr).Value = ComboBox3
  Range("D" & lr).Value = TextBox1
  Range("E" & lr).Value = TextBox2
  Range("F" & lr).Value = TextBox3
  ComboBox1 = ""
  ComboBox2 = ""
  ComboBox3 = ""
  TextBox1 = ""
  TextBox2 = ""
  TextBox3 = ""
  MsgBox "Done"
End Sub

 

[abdelfattah]

Try this

Private Sub CommandButton1_Click()
  If ComboBox1 = "" Then
    MsgBox "Fill combo1"
    ComboBox1.SetFocus
    Exit Sub
  End If
  Dim lr As Long
  lr = Range("A" & Rows.Count).End(xlUp).Row + 1
  Range("A" & lr).Value = ComboBox1
  Range("B" & lr).Value = ComboBox2
  Range("C" & lr).Value = ComboBox3
  Range("D" & lr).Value = TextBox1
  Range("E" & lr).Value = TextBox2
  Range("F" & lr).Value = TextBox3
  ComboBox1 = ""
  ComboBox2 = ""
  ComboBox3 = ""
  TextBox1 = ""
  TextBox2 = ""
  TextBox3 = ""
  MsgBox "Done"
End Sub

thank so much the code really works but i have a simple problem i would begins from a23:f23 in my case begins from a40:f40 i no know why

 

[DanteAmor]

thank so much the code really works but i have a simple problem i would begins from a23:f23 in my case begins from a40:f40 i no know why

This line in the macro "reviews" column A from the bottom up to find the last cell with data.

lr = Range("A" & Rows.Count).End(xlUp).Row + 1

If the code begins to write in row 40, it means that on the A39 you have blank spaces, check column A and delete the cells that internally have blank spaces.

 

[abdelfattah]

This line in the macro "reviews" column A from the bottom up to find the last cell with data.

lr = Range("A" & Rows.Count).End(xlUp).Row + 1

If the code begins to write in row 40, it means that on the A39 you have blank spaces, check column A and delete the cells that internally have blank spaces.

actually i would transfer data from a23:f23 of course this range is empty then when i fill new process automatically add a new rows is a24: f24 to fill a new data
by the way i fill data manually from a23: f23 whe i fill a new data it supposes to begins from a24:f24 but it begins from a40: f40 it still the problem continues

 

[DanteAmor]

Perform the test on a new sheet, put a heading in cell A22.

Did you modify the code?

 

[abdelfattah]

Perform the test on a new sheet, put a heading in cell A22.

Did you modify the code?

i attache image of file mybe you know where is the problem

https://ufile.io/7z3uxb7c

 

[DanteAmor]

I don't understand the image, I don't see the rows or the columns.
You can do the test on a new sheet.
Or what is the problem?
Do you have anything in row 39? Any data or a formula? or subtotals?


You have to do the test on a new sheet. For you to understand the operation of the code.

Try this

[/B]Private Sub CommandButton1_Click()
  If ComboBox1 = "" Then
    MsgBox "Fill combo1"
    ComboBox1.SetFocus
    Exit Sub
  End If
  Dim lr As Long
  'lr = Range("A" & Rows.Count).End(xlUp).Row + 1
  lr = 20 'row start
  Do While Range("A" & lr) <> ""
    lr = lr + 1
  Loop
  Range("A" & lr).Value = ComboBox1
  Range("B" & lr).Value = ComboBox2
  Range("C" & lr).Value = ComboBox3
  Range("D" & lr).Value = TextBox1
  Range("E" & lr).Value = TextBox2
  Range("F" & lr).Value = TextBox3
  ComboBox1 = ""
  ComboBox2 = ""
  ComboBox3 = ""
  TextBox1 = ""
  TextBox2 = ""
  TextBox3 = ""
  MsgBox "Done"
End Sub
[B]

 

[abdelfattah]

you're right about a new sheet the problem in my file your codes are perfect and i'm really sorry if i waste your time thanks a lot

 

[DanteAmor]

Dont worry. Im glad to help you, thanks for the feedback.