You could use the
Range.Offset property[
^]
Keep track of which column you are up to and just increase the column offset by that number. It's the address you want to increase, not the Value
E.g.
If Me.TextBox1.Text = "" Then
MsgBox ("Cannot Be Empty!")
Else
For lRow = 3 To 100
Worksheets("Sheet1").Range("A1").Offset(lRow - 1, 0).Value = TextBox1.Text
Next lRow
End If
If Me.TextBox2.Text = "" Then
MsgBox ("Cannot Be Empty!")
Else
For lRow = 3 To 100
Worksheets("Sheet1").Range("A1").Offset(lRow - 1, 1).Value = TextBox2.Text
Next lRow
End If
Note the use of
lRow - 1
and not
lRow
.. because we are
offsetting from our start point, not using an absolute address.
You could also use the
AutoFill [
^] method here instead of the loops