VBA Entering userform data at next blank row correctly

Question

1. Created a userform
2. Added a textBox and a comboBox
3. Added a submit button
4. When submit is clicked it adds the data to a spreadsheet

From what I have been told and what I have read this is wrong

ActiveCell.Value = TextBox3.Text 
ActiveCell.Offset(0, 1).Select   
ActiveCell.Value = ComboBox1.Text  
ActiveCell.Offset(1, -1).Select  

This works but I've been told I shouldn't use the .select keyword when possible. I've read that to make my code reusable I should create variables. How would a professional developer write this code, can it be written in less lines and how can I refer to the activecell offset without using select?

Answer 1

I am assuming you want TextBox3 in column A and ComboBox1 in column B. If you want different columns just change the letter references.

Sub OnClick() 'whatever your current sub is called.

    Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("Name of Sheet where data is going")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

    ws.Range("A" & LastRow).Value = TextBox3.Text 'Adds the TextBox3 into Col A & Last Blank Row
    ws.Range("B" & LastRow).Value = ComboBox1.Text 'Adds the ComboBox1 into Col B & Last Blank Row

End Sub

If you want a method using Offset():

Sub OnClickwithOffset() 'whatever your current sub is called.

    Dim LastRow As Long, ws As Worksheet

    Set ws = Sheets("Name of Sheet where data is going")

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row

    ws.Range("A" & LastRow).Value = TextBox3.Text 'Adds the TextBox3 into Col A & Last Blank Row
    ws.Range("A" & LastRow).Offset(0, 1).Value = ComboBox1.Text 'Adds the ComboBox1 into next cell to the right of TextBox3 data.

End Sub