Last Value into Textbox

[perku]

Please help!!

I have a userform for data entry and i want the textbox3 on this userform to display the last entry (an 8 digit code) in column E when the commandbutton is pressed to enter the data.
i am using the below code and cannot get it to work.

TextBox3.Value = Worksheets("Sheet1").Range("E1").End(xlDown).Value

Using excel 2013

Ryan

 

[Norie]

Does this work?

TextBox3.Value = Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Value

 

[perku]

Thanks for the reply

Unfortunately not, is there some way i can post the spreadsheet?

 

[Norie]

How/where/when are you running the code?

 

[perku]

It is at the bottom of a commandbutton click

 

[perku]

Private Sub CommandButton1_Click()
If Len(TextBox1.Value) < 33 Then
MsgBox "Invalid Product Barcode"
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
Else
If Len(TextBox2.Value) < 5 Then
MsgBox "Invalid Location Barcode"
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
Else
If Len(TextBox2.Value) > 5 Then
MsgBox "Invalid Location Barcode"
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
Else
Dim LastRow As Object


Set LastRow = Sheet1.Range("a65536").End(xlUp)


LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text

TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Value = Worksheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Value
TextBox4.Value = Worksheets("Sheet1").Range("g1").End(xlDown).Value
TextBox5.Value = Worksheets("Sheet1").Range("h1").End(xlDown).Value
TextBox6.Value = Worksheets("Sheet1").Range("f1").End(xlDown).Value
TextBox7.Value = Worksheets("Sheet1").Range("j1").End(xlDown).Value
TextBox8.Value = Worksheets("Sheet1").Range("i1").End(xlDown).Value
TextBox1.SetFocus


End If
End If
End If
End Sub

 

[mikerickson]

Do any of those columns contain formulas or are they constants?

 

[perku]

Yes

Columns E to J are parts of the the values in column A and B which the userform is inserting. ie column E = "=LEFT(A2,8)".

 

[mikerickson]

If all the text box's values are supposed to come from the same row, you could use this

With Sheets("Sheet1").Range("E:E")
    With .Find("?*", after:=.Cells(1, 1), LookIn:=xlValue, lookat:=xlWhole, searchdirection:=xlPrevious)
        TextBox3.Text = .Value
        TextBox4.Text = .Offset(0, 2).Value
        TextBox5.Text = .Offset(0, 3).Value
        TextBox6.Text = .Offset(0, 1).Value
        TextBox7.Text = .Offset(0, 5).Value
        TextBox8.Text = .Offset(0, 4).Value
    End With
End With

If not, each text box would need its own .Find line

 

[perku]

i get a run-time error '9' on the second line.

Anywhere in particular to place that code?