CODEWITHSUNDEEP
excelvba
Jp4Real
Jp4Real

Reputation: 2002

Have Excel switch to next row if first row is populated

I'm learning Excel VBA and I just hit a wall.

I have a spreadsheet in which I built a UserForm. This userform populates Row 14 from A to AK.

Now I've been able to copy the data from the form using this code (part of it it's way to long to copy completely here):

'Date
Sheet1.Range("A14") = DTPicker1
'AC Type
Sheet1.Range("b14") = ComboBox1
'AC Reg
Sheet1.Range("C14") = TextBox3
'Pic
Sheet1.Range("d14") = TextBox5
'co-pilot
Sheet1.Range("e14") = TextBox4
'from
Sheet1.Range("f14") = TextBox7
'to
Sheet1.Range("g14") = TextBox6
'remarks
Sheet1.Range("h14") = TextBox8

Now what I would like to do is that IF A14 is populated, well everything that writes in 14 switches to 15, and so on.

Is there any way I can do this?

Upvotes: 2

Views: 85

Answers (1)

transistor1
transistor1

Reputation: 2925

Something along these lines should work (untested):

Static rng As Range
If rng Is Nothing Then
  Set rng = Sheet1.Range("A14")
  Do While rng.Value <> ""
     Set rng = rng.Offset(1)
  Loop
End If

'Date
rng.Range("A1").Value = DTPicker1
'AC Type
rng.Range("B1").Value = ComboBox1
'AC Reg
rng.Range("C1").Value = TextBox3
'Pic
rng.Range("D1").Value = TextBox5
'co-pilot
rng.Range("E1").Value = TextBox4
'from
rng.Range("F1").Value = TextBox7
'to
rng.Range("G1").Value = TextBox6
'remarks
rng.Range("H1").Value = TextBox8

'Point to the next line
Set rng = rng.Offset(1)

Upvotes: 2

Related Questions