Userform info doesn't move past first row?

[coffeeandbooks]

So I'm super new to VBA (hey but I'm learning!)

The goal of the code that I'm writing is to create a simple database to track new corporate accounts and sales based on office location. (So nothing fancy).

However, as the user fills out the user form, it only fills out the first now (doesn't move onto the next row, etc)...

Here's the code I have written so far:

Sub CommandButton1_Click()
Dim i As Integer
i = 1
While ThisWorkbook.Worksheets("Sheet1").Range("A" & i).Value <> ""
i = i + 1
Wend
ThisWorkbook.Worksheets("Sheet1").Range("B" & i).Value = ComboBox1.Value
ThisWorkbook.Worksheets("Sheet1").Range("C" & i).Value = ComboBox2.Value
ThisWorkbook.Worksheets("Sheet1").Range("D" & i).Value = TextBox1.Value
ThisWorkbook.Worksheets("Sheet1").Range("E" & i).Value = TextBox2.Value
ThisWorkbook.Worksheets("Sheet1").Range("F" & i).Value = TextBox3.Value
ThisWorkbook.Worksheets("Sheet1").Range("G" & i).Value = TextBox4.Value
ThisWorkbook.Worksheets("Sheet1").Range("H" & i).Value = TextBox5.Value
End Sub


Sub UserForm_Initialize()
ComboBox1.List = Array("001 - S. Venice", "056 - Seminole", "042 - Gateway")
ComboBox2.List = Array("1:00PM", "5:00PM", "9:00PM")
End Sub

Thanks in advance!

 

[Trebor76]

Hi coffeeandbooks,

Welcome to MrExcel!!

It looks like the issue is that you're scrolling down column A to find the first blank row but you're populating columns B to H (inclusive). Assuming that is the issue try this:

Option Explicit
Sub CommandButton1_Click()
    
    Dim i As Long
    
    With ThisWorkbook.Worksheets("Sheet1")
        i = .Range("B:H").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
        .Range("B" & i).Value = ComboBox1.Value
        .Range("C" & i).Value = ComboBox2.Value
        .Range("D" & i).Value = TextBox1.Value
        .Range("E" & i).Value = TextBox2.Value
        .Range("F" & i).Value = TextBox3.Value
        .Range("G" & i).Value = TextBox4.Value
        .Range("H" & i).Value = TextBox5.Value
    End With
    
End Sub

Regards,

Robert