Code doesn't go on a new line (emptyRow)

[Patriot2879]

Hi please can you help me with the code below, it doesnt seem to put data on a new line and go down 1 column. instead it overwrites the previous data once the command button is clicked on. Hope you can help me please?

Private Sub CommandButton4_Click()
Dim emptyRow As Long

With ThisWorkbook.Sheets("Additional Job")
   emptyRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

   .Cells(emptyRow, 3).Value = TextBox1.Value
   .Cells(emptyRow, 2).Value = TextBox10.Value
   .Cells(emptyRow, 1).Value = ComboBox2.Value
   .Cells(emptyRow, 5).Value = TextBox2.Value
   .Cells(emptyRow, 6).Value = TextBox11.Value
   .Cells(emptyRow, 7).Value = TextBox3.Value
   .Cells(emptyRow, 13).Value = ComboBox4.Value
   .Cells(emptyRow, 12).Value = ComboBox3.Value
   .Cells(emptyRow, 14).Value = TextBox4.Value
   .Cells(emptyRow, 15).Value = TextBox12.Value
   .Cells(emptyRow, 17).Value = TextBox5.Value
   .Cells(emptyRow, 18).Value = TextBox6.Value
   .Cells(emptyRow, 19).Value = TextBox7.Value
   .Cells(emptyRow, 20).Value = TextBox8.Value
   .Cells(emptyRow, 21).Value = TextBox9.Value
End With
Unload Me



End Sub

 

[Z51]

Your code is checking column A for the last non-empty cell, starting at the bottom. As soon as it finds a non-empty cell it adds 1 and uses that row. In the rest of your code, the value in column A is derived from the value in "ComboBox2". If nothing is selected in that combobox, nothing will get written into column A on your worksheet. In that case, it will keep overwriting that row until you DO pick a value in ComboBox2. At that point it should go to the next row when the button is clicked.

If you always want to make sure your data goes into a new row, check a column that will ALWAYS have a value after you click the button. For example, if TextBox1 will always have a value, check column 3 (C) instead:

emptyRow = Cells(Rows.Count, "C").End(xlUp).Row + 1

 

[Fluff]

On this line

emptyRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

you need to add a full stop before the word cells as you have on the other lines.

 

[Patriot2879]

Hi thanks for the reply, that’s so odd then as column A always has data in it, but still gets overwritten. I’d there anything else in the code that can be changed?

 

[Fluff]

Did you see my suggestion?

 

[RustamMika]

Try

emptyRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1

instead of

emptyRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

 

[Patriot2879]

Did you see my suggestion?

Thank you that now works

 

[Patriot2879]

Try

emptyRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1

instead of

emptyRow = Cells(Rows.Count, "A").End(xlUp).Row + 1

Thank you that now works

 

[Fluff]

You're welcome & Thanks for the feedback.