Simple question - Simple Solution - Last Row

[erutherford]

I have a table with headers (starting in A1), nothing fancy. I have a form that controls the data that is entered, works fine.

If the table is empty with only the headers there, I get an error (at the Range statement). If cell A2 has something in it it works fine and finds the last row.

Private Sub CommandButton1_Click()
Range("A1").End(xlDown).Offset(1, 0).Select
'FN
If TextBox1.Value = "" Then
 
    MsgBox "First Name Required!", vbCritical
    UserForm1.TextBox1.SetFocus
    Exit Sub
 
End If

'LN
If TextBox2.Value = "" Then
 
    MsgBox "Last Name Required!", vbCritical
    UserForm1.TextBox2.SetFocus
    Exit Sub

End If

'US - Enters Data to Table
   
ActiveCell = TextBox1.Value 'FN
ActiveCell.Offset(0, 1) = TextBox2.Value 'LN

Unload UserForm2

End Sub

has to be something simple and I am looking right at it! Just don't see it!

 

[njimack]

If I've understood correctly, this might be what you're after...

Private Sub CommandButton1_Click()
Dim LastRow As Long
 If TextBox1.Value = "" Then
    MsgBox "First Name Required!", vbCritical
    UserForm1.TextBox1.SetFocus
    Exit Sub
End If
If TextBox2.Value = "" Then
    MsgBox "Last Name Required!", vbCritical
    UserForm1.TextBox2.SetFocus
    Exit Sub
End If
LastRow = Cells(Rows.Count, 1).End(xlUp).Offset(1)
Cells(LastRow, 1) = TextBox1.Value
Cells(LastRow, 2) = TextBox2.Value
 Unload UserForm2
 End Sub

 

[erutherford]

Nope that produces the same error at

Cells(LastRow, 1) = TextBox1.Value

 

[erutherford]

found this code which seems to have solved the issue

<code>
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
</code>

Counting up is a better fit, than counting down.

Joe4 posted this explanation:

Is there any data in column A past row 1 to start with?
If not, Range("A1").End(xldown) will take you down to your last available row on your worksheet, but then the Offset(1,0) will try to take you down one more row, which is impossible if you are on the last possible row already.

Perhaps this version will work better (it works from the bottom up rather than the top down)?
Cells(Rows.Count,"A").End(xlUp).Offset(1,0).Select

 

[RoryA]

Neil's code did exactly the same thing, except it avoided inefficiently selecting, so it should have produced the same result.

 

[erutherford]

Rory
I did read where "select" is not the best way to go. But the "cells(LastRow)" creates an error at the first line ('FN)

 MsgBox "Information Complete, Thank You"
    Unload UserForm1

'US - Enters Data to Table

Cells(LastRow, 1) = TextBox1.Value 'FN
Cells(LastRow, 2) = TextBox2.Value 'LN
Cells(LastRow, 3) = TextBox3.Value 'Add1
Cells(LastRow, 4) = TextBox4.Value 'Add2
Cells(LastRow, 5) = TextBox5.Value 'Add3
Cells(LastRow, 6) = TextBox6.Value 'City
Cells(LastRow, 7) = TextBox7.Value 'State
Cells(LastRow, 8) = TextBox8.Value 'Zip
Cells(LastRow, 9) = TextBox9.Value 'Country
Cells(LastRow, 10) = TextBox10.Value 'Email
Cells(LastRow, 11) = TextBox11.Value 'Ph1
Cells(LastRow, 12) = TextBox12.Value 'Ph2
Cells(LastRow, 13) = DTPicker1.Value 'Start Time
Cells(LastRow, 14) = DTPicker2.Value 'End Tme
Cells(LastRow, 15) = TextBox13.Value 'Notes

Time to do some investigating I guess

 

[njimack]

What error were you getting with my code?

 

[RoryA]

Where's the code to populate Lastrow?

 

[erutherford]

Rory & Neil,
Here is the entire code for the command button. It just a basic contact form.

Private Sub CommandButton1_Click()

'FN
If TextBox1.Value = "" Then
 
    MsgBox "First Name Required!", vbCritical
    UserForm1.TextBox1.SetFocus
    Exit Sub
 
End If

'LN
If TextBox2.Value = "" Then
 
    MsgBox "Last Name Required!", vbCritical
    UserForm1.TextBox2.SetFocus
    Exit Sub

End If

'Add1
 If TextBox3.Value = "" Then
 
    MsgBox "Address 1 Required!", vbCritical
    UserForm1.TextBox3.SetFocus
    Exit Sub
 
End If
'Add2
If TextBox4.Value = "" Then
 
    MsgBox "Address 2Required!", vbCritical
    UserForm1.TextBox4.SetFocus
    
    Exit Sub

End If
    
'Add3
If TextBox5.Value = "" Then

    MsgBox "Not Required", vbInformation
 
    Exit Sub
 
End If
 
'City
If TextBox6.Value = "" Then
 
    MsgBox "City Required!", vbCritical
    UserForm1.TextBox6.SetFocus
    
    Exit Sub
End If
 
'State
If TextBox7.Value = "" Then
 
    MsgBox "State Required!", vbCritical
    UserForm1.TextBox7.SetFocus
    
    Exit Sub
End If
    
'Zip
If TextBox8.Value = "" Then
 
    MsgBox "Zip Code Required!", vbCritical
    UserForm1.TextBox8.SetFocus
    
    Exit Sub

End If

'Country
If TextBox9.Value = "" Then
 
    MsgBox "Country Required!", vbCritical
    UserForm1.TextBox9.SetFocus
    
    Exit Sub
 
End If
 
'Email
If TextBox10.Value = "" Then
 
    MsgBox "Email Required!", vbCritical
    UserForm1.TextBox10.SetFocus
    
    Exit Sub
    
End If

'Ph1
If TextBox11.Value = "" Then
 
    MsgBox "Phone Number Required!", vbCritical
    UserForm1.TextBox11.SetFocus
    Exit Sub
 
End If
 
'Ph2
If TextBox12.Value = "" Then
    
    MsgBox "Not Required", vbInformation
    UserForm1.TextBox12.SetFocus
    Exit Sub
    
End If

'Start Time
If DTPicker1.Value = "" Then
 
    MsgBox "Start Time Required!", vbCritical
    UserForm1.DTPicker1.SetFocus
    Exit Sub
 
End If
 
'End Time
If DTPicker2.Value = "" Then
 
    MsgBox "End Time Required!", vbCritical
    UserForm1.DTPicker2.SetFocus
    Exit Sub
    
End If

'Notes
If TextBox13.Value = "" Then
    
    MsgBox "Not Required", vbInformation
    Exit Sub
 
End If
    
    MsgBox "Information Complete, Thank You"
    

'US - Enters Data to Table


Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select

ActiveCell = TextBox1.Value 'FN
ActiveCell.Offset(0, 1) = TextBox2.Value 'LN
ActiveCell.Offset(0, 2) = TextBox3.Value 'Add1
ActiveCell.Offset(0, 3) = TextBox4.Value 'Add2
ActiveCell.Offset(0, 4) = TextBox5.Value 'Add3
ActiveCell.Offset(0, 5) = TextBox6.Value 'City
ActiveCell.Offset(0, 6) = TextBox7.Value 'State
ActiveCell.Offset(0, 8) = TextBox8.Value 'Zip
ActiveCell.Offset(0, 9) = TextBox9.Value 'Country
ActiveCell.Offset(0, 10) = TextBox10.Value 'Email
ActiveCell.Offset(0, 11) = TextBox11.Value 'Ph1
ActiveCell.Offset(0, 12) = TextBox12.Value 'Ph2
ActiveCell.Offset(0, 13) = DTPicker1.Value 'Start Time
ActiveCell.Offset(0, 14) = DTPicker2.Value 'End Tme
ActiveCell.Offset(0, 15) = TextBox13.Value 'Notes

Unload UserForm2

End Sub

Neil when I remove the "Active cell" code and replace with "cells(LastRow)" and run it the first line is highlighted yellow (Cells(LastRow, 1) = TextBox1.Value 'FN) and data is not transferred.
Rory the code to populate starts after "'US - Enters Data to Table" if I understand your question.

 

[RoryA]

when I remove the "Active cell" code and replace with "cells(LastRow)" and run it the first line is highlighted yellow (Cells(LastRow, 1) = TextBox1.Value 'FN) and data is not transferred.

It would, if you don't add the line from Neil's earlier post to assign a value to Lastrow.