Help Moving data down 1 row

srdear

New Member
Joined
Apr 20, 2014
Messages
4
Hi there.... I'm new to this forum so be gentle.

I have a Rental Form that i'm working on, and to tidy things up when I print it out, I would like to move a cells data down 1 row if that cell is empty. Here's what I have:
cell b8 - First and Last Name
cell b9 - Address1
cell B10 - Address2
cell b11 - City, State, Zip
cell 12 - Phone #
etc.

Not too many people have an address2, so when that field is empty, I'd like to move Address1's data down to it's positon (address2). If they do, and address2 has data in it, leave address1 where it is. Simple right? This moving would happen when the command button is hit and the form data goes to sheet 1 which works fine.
Any help would be great!
Thanks in advance

Steve
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
You may want to start by posting your existing code. The move portion of the macro will need to be integrated into that code.
 

srdear

New Member
Joined
Apr 20, 2014
Messages
4
Below is the vb code I'm using in the form.



Private Sub CommandButtonNext_Click()
'Name
Range("B8").Value = TextBox1.Value
'Address1
Range("B9").Value = TextBox2.Value
'address2
Range("B10").Value = TextBox3.Value
'combines city,state,zip,textboxes data to 1 cell with spaces & comma
Range("B11").Value = TextBox4.Value & ", " & TextBox5.Value & " " & TextBox6.Value
'phone
Range("B12").Value = TextBox7.Value
'dl#
Range("B13").Value = TextBox8.Value

Unload UserForm1
UserForm2.Show
End Sub

Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'formats phone number as its entered
Dim Char As Long
Dim I As Long
Dim N As Long

If KeyAscii < 48 Or KeyAscii > 57 Then
KeyAscii = 0
Else
For I = 1 To Len(TextBox7.Text)
Char = Asc(Mid(TextBox7.Text, I, 1))
If Char >= 48 And Char <= 57 Then N = N + 1
Next I

Select Case N
Case 3
If Right(TextBox7.Text, 1) <> "-" Then TextBox7.Value = TextBox7.Value & ")" & " "
If Left(TextBox7.Text, 1) <> "(" Then TextBox7.Value = "(" & TextBox7.Value
Case 6
If Right(TextBox7.Text, 1) <> "-" Then TextBox7.Value = TextBox7.Value & "-"
Case 9
If Left(TextBox7.Text, 1) <> "(" Then TextBox7.Value = "(" & TextBox7.Value
If Mid(TextBox7.Text, 5, 1) = "-" Then
TextBox7.Value = Left(TextBox7.Value, 4) & ")" & Mid(TextBox7.Text, 6, 8)
End If
End Select
End If


End Sub

Private Sub CommandButtonClear_Click()
'clears all fields
Dim z As Control
For Each z In UserForm1.Controls
If TypeName(z) = "TextBox" Then
z.Value = ""
End If
Next z
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,108,749
Messages
5,524,609
Members
409,593
Latest member
anand31

This Week's Hot Topics

Top