Starting in a new row when filling in a new form

[beanhead0321]

I have a UserForm with 4 textboxes on it. When I press the Okay commandbutton, it puts the text in these 4 textboxes into cells B2, C2, D2, and E2, and the date in A2, then clears the form. However, I don't know how to make it so that the next time the form is filled out, the data goes into B3, C3, D3, and E3 and the date into A3 and so on. I think I have to use a Loop, but I'm a VBA noobie and I don't know how. Thanks in advanced. This is the code that I have now for my commandbutton.


Private Sub CommandButton1_Click()

Range("A2").Value = Date
Range("B2").Value = TextBox1.Value
Range("C2").Value = TextBox2.Value
Range("D2").Value = TextBox3.Value
Range("E2").Value = TextBox4.Value

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""


End Sub

 

[Ruddles]

Try:-

Private Sub CommandButton1_Click()
 
Dim iNext As Long
 
iNext = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row + 1
 
Range("A" & iNext).Value = Date
Range("B" & iNext).Value = TextBox1.Value
Range("C" & iNext).Value = TextBox2.Value
Range("D" & iNext).Value = TextBox3.Value
Range("E" & iNext).Value = TextBox4.Value
 
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

End Sub

 

[beanhead0321]

That worked wonderfully! Can you explain how the i works and stuff? Or what the code is doing in general?

 

[Ruddles]

[COLOR=red]Dim iNext As Long
[/COLOR] 
[COLOR=blue]iNext = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row + 1
[/COLOR] 
[COLOR=magenta]Range("A" & iNext).Value = Date
[/COLOR]

The Dim statement sets up a variable called iNext. The variable is an integer type - no decimal places because row numbers are whole numbers - with a maximum value of two billion - more than enough for Excel's maximum row count.

The next line goes to the end of the sheet in column A, then does a virtual Ctrl-PgUp which jumps to the last non-blank cell in the column. Then it adds 1 to it so it's looking at the first blank cell in column A and stores that row number in iNext.

So now iNext holds the number of the first empty row in your worksheet.

Then, where your code always used row 2 (Range("A2")), my code replaced the 2 with the value of iNext, so when the worksheet has no data in it - just the column headings - iNext has a value of 2, so would be like saying Range("A2"). When row 2 becomes filled and iNext takes a value of 3, the code would be like saying Range("A3").

Get it?

This is a standard method for working your way up/down/across a worksheet: you work out where you want to be (or where you need to start and finish), then set a variable to that value (or loop the variable from the start value to the finish value), and use that variable in a Cells/Range/Rows/Columns reference.

 

[beanhead0321]

Sweet. Thank you very much =D