83040

Incrementing cell row in excel

Question:

I have a UserForm that's going to submit data from the fields to columns A, B, and C, but I need it to move down and fill in the next empty row every time a user hits submit.

This is what I have so far, I don't know what I would put in to make it so it would go from A2/B2/C2 to A3/B3/C3, etc.

Private Sub Submit_Click() Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) Sheet1.Range("A2").Value = MatchNum.Text Sheet1.Range("B2").Value = TeamNum.Text Sheet1.Range("C2").Value = AllianceTeamNum.Text MsgBox "One record written to Sheet1" End Sub

I'm a complete beginner at Visual Basic (approx. 1 hr. of experience) and it'd be nice if the solution is as simple as possible. Any help would be much appreciated!

Answer1:

Try below code :

Private Sub Submit_Click() With Sheet1 .Select Dim LastRow As Long LastRow = .Range("A65536").End(xlUp).Row + 1 .Range("A" & LastRow).Value = MatchNum.Text .Range("B" & LastRow).Value = TeamNum.Text .Range("C" & LastRow).Value = AllianceTeamNum.Text End With MsgBox "One record written to Sheet1" End Sub

Answer2:

Try this:

Dim start As Integer Private Sub CommandButton1_Click() Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) Sheet1.Range("A" & start).Value = "a" Sheet1.Range("B" & start).Value = "b" Sheet1.Range("C" & start).Value = "c" MsgBox "One record written to Sheet1" start = start + 1 End Sub Private Sub UserForm_Initialize() start = 2 End Sub

Recommend

  • How to Add Column in Final Excel file from merged excel files
  • How to NOT skip empty rows or cells in vba?
  • Enter a new line and copy formula from cells above
  • Change column width and row height of hidden columns and rows (remaining hidden): Excel VBA
  • Excel VBA delete entire row if both columns B and C are blank
  • Loop paste formula until next cell in range is empty
  • vba userform , if any of the checkboxes in the frame is true then macro should not be applied on the
  • VBA Multiple loops match conditions
  • Excel: how to create a number in a column according to a text value in other column?
  • Aggregating based on “near” row values
  • VBA: How can I keep only the date values from a string?
  • What is the difference in Angular2 between inject a provider in @Component and @Module?
  • Omit template arguments when create an instance of template class from another instance of template
  • Retrieve List of Cells in Excel Where Column Equals Something
  • Check if one value in one column is in another column
  • vba paste values and keep source formatting?
  • Erase empty cells in an array
  • Does TMonitor.GetBoundsRect have an access violation bug in Delphi 2007 triggered by VNC?
  • Delete All Rows With Errors in a column
  • Fortran: Array of arbitrary dimension?
  • Range Multiplication VB.NET (What is wrong with this code?)
  • How to run a query between dates and times?
  • Tips for creating scalable WPF user control
  • Maven, ActionBarSherlock v4 and Roboguice v2 - How do I get it to build?
  • JOOQ nested condition
  • Why is this button causing my layout to break?
  • Double-click autofill - dynamic based on adjacent cell
  • Excel VBA URLDownloadToFile Error for https Ressource
  • Creating a Multidimensional, Associative Array in VBScript
  • joining two bezier curves
  • Implementation of State Monad
  • Conversion from string “a” to type 'Boolean' is not valid
  • Excel's Macro-Recorder usage
  • How to handle AllServersUnavailable Exception
  • Javascript + PHP Encryption with pidCrypt
  • VBA Convert delimiter text file to Excel
  • Getting Messege Twice Using IMvxMessenger
  • Reading document lines to the user (python)
  • How can i traverse a binary tree from right to left in java?
  • How can I use threading to 'tick' a timer to be accessed by other threads?