Insert userform data into next column on respective rows

Question

I want to add the values from a form to an Excel sheet.

I have the "headers" in column A from A1 down to A20 with A21 a "score" field that runs from B21 to CZ21 that automatically calculates a score based on the values entered above in each respective column.

A1 through 20 has the headers for the questions and I want the values from the form entered initially in B1 through 20 and then C1-20 and so on and so forth.

As an example, the first form response should be entered into rows B1 to B20 with each row having a value. The second form response will be entered into rows C1 to C20 with each row having it's own value.

Column A is a frozen pane.

Private Sub SaveButton_Click()

'Make Sheet2 active
Sheet4.Activate

'Determine empty Row
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Cells(emptyRow, 2).Value = TextBox1.Value

Cells(emptyRow, 3).Value = Format(Now)

Cells(emptyRow, 4).Value = TextBox3.Value

If CheckBox1.Value = True Then
Cells(emptyRow, 5).Value = CheckBox1
Else
Cells(emptyRow, 5).Value = CheckBox1
End If

If CheckBox2.Value = True Then
Cells(emptyRow, 6).Value = CheckBox2
Else
Cells(emptyRow, 6).Value = CheckBox2
End If

If CheckBox3.Value = True Then
Cells(emptyRow, 7).Value = CheckBox3
Else
Cells(emptyRow, 7).Value = CheckBox3
End If

If CheckBox4.Value = True Then
Cells(emptyRow, 8).Value = CheckBox4
Else
Cells(emptyRow, 8).Value = CheckBox4
End If

If CheckBox9.Value = True Then
Cells(emptyRow, 9).Value = CheckBox9
Else
Cells(emptyRow, 9).Value = CheckBox9
End If

If CheckBox11.Value = True Then
Cells(emptyRow, 10).Value = CheckBox11
Else
Cells(emptyRow, 10).Value = CheckBox11
End If

If CheckBox14.Value = True Then
Cells(emptyRow, 11).Value = CheckBox14
Else
Cells(emptyRow, 11).Value = CheckBox14
End If

If CheckBox16.Value = True Then
Cells(emptyRow, 12).Value = CheckBox16
Else
Cells(emptyRow, 12).Value = CheckBox16
End If

If CheckBox18.Value = True Then
Cells(emptyRow, 13).Value = CheckBox18
Else
Cells(emptyRow, 13).Value = CheckBox18
End If

If CheckBox20.Value = True Then
Cells(emptyRow, 14).Value = CheckBox20
Else
Cells(emptyRow, 14).Value = CheckBox20
End If

If CheckBox22.Value = True Then
Cells(emptyRow, 15).Value = CheckBox22
Else
Cells(emptyRow, 15).Value = CheckBox22
End If

If CheckBox24.Value = True Then
Cells(emptyRow, 16).Value = CheckBox24
Else
Cells(emptyRow, 16).Value = CheckBox24
End If

If CheckBox26.Value = True Then
Cells(emptyRow, 17).Value = CheckBox26
Else
Cells(emptyRow, 17).Value = CheckBox26
End If

If CheckBox27.Value = True Then
Cells(emptyRow, 18).Value = CheckBox27
Else
Cells(emptyRow, 18).Value = CheckBox27
End If

If CheckBox28.Value = True Then
Cells(emptyRow, 19).Value = CheckBox28
Else
Cells(emptyRow, 19).Value = CheckBox28
End If

Cells(emptyRow, 20).Value = TextBox5.Value

Cells(emptyRow, 21).Value = TextBox4.Value

'Clearing data

CheckBox1.Value = "False"
CheckBox2.Value = "False"
CheckBox3.Value = "False"
CheckBox4.Value = "False"
CheckBox9.Value = "False"
CheckBox11.Value = "False"
CheckBox14.Value = "False"
CheckBox16.Value = "False"
CheckBox18.Value = "False"
CheckBox20.Value = "False"
CheckBox22.Value = "False"
CheckBox24.Value = "False"
CheckBox26.Value = "False"
CheckBox27.Value = "False"
CheckBox28.Value = "False"
TextBox1.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""

End Sub

Answer 1

You can do something like this

Private Sub SaveButton_Click()

    Dim col as Range

    Set col = Sheet4.Range("B1:B20")  'first potential location
    'find first unused column
    Do While Application.CountA(col) > 0
        Set col = col.Offset(0, 1)
    Loop

    col.cells(1).Value = TextBox1.Value
    col.cells(2).Value = Format(Now)
    col.cells(3).Value = TextBox3.Value
    'etc etc