I currently have a formula in Columns A,B,C,D,E. I currentltly have a table from F21:AB5000.

I need my VBA code to insert data from a userform into the next available row.

Any help please? I currently have it to input data into a new worksheet, then I use that data to specify into my original worksheet and sometimes the data format is wrong.

1 Answer

This VBA macro helps to enter new records in next empty row in the TABLE.

Private Sub CommandButton1_Click() Dim rng As Range Set rng = ActiveSheet.ListObjects("Table1").Range Dim LastRow As Long LastRow = rng.Find(What:="*", _ After:=rng.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row rng.Parent.Cells(LastRow + 1, 1).Value = TextBox1.Value rng.Parent.Cells(LastRow + 1, 2).Value = TextBox2.Value
End Sub

N.B.

FIND method has been used to search the next blank row in a table for data entry.

---

This optional VBA code can be used to initialize User's Form controls for next entry.

Private Sub CommandButton2_Click() Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then ctl.Value = "" End If
Next ctl
End Sub

N.B.

• Command line can be modified for other controls like ComboBox/ListBox.

Like,

If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy