Object Variable or With Block Not Set- Userform

Question

I am pretty new to VBA, I have a userform created and all I want it to do is after the enter button is clicked, it adds the filled-in text boxes onto the next empty row in the excel sheet. I get object variable not set (on the rw = ws.Cells line) and I'm not sure how to set it.

Thanks so much.

Private Sub enter_Click()
Dim rw As Integer
Dim ws As Worksheet
Set ws = Worksheets("Home")
rw = ws.Cells.Find(What:=” * ”, SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
ws.Cells(rw, 1).Value = Me.TextBox1.Value
ws.Cells(rw, 2).Value = Me.TextBox2.Value
End Sub

SJR · Accepted Answer · 2019-11-21 11:27:51Z

2

Try this.

You need straight quotes rather than those curly ones.

When using Find always build in a check that your term is found otherwise you will get an error when trying to assign a row to Nothing.

Also set the After parameter if you are looking for the first unused row - start at the top and work up, i.e. loop back round to the bottom and up.

Private Sub enter_Click()

Dim rw As Long  'rather than integer
Dim ws As Worksheet
Dim r As Range

Set ws = Worksheets("Home")
Set r = ws.Cells.Find(What:="*", after:=ws.Cells(1), SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues)
'alternatively could use rw=ws.range("A" & rows.count).end(xlup).row + 1

If Not r Is Nothing Then
    ws.Cells(r.row+1, 1).Value = Me.TextBox1.Value
    ws.Cells(r.Row + 1, 2).Value = Me.TextBox2.Value
End If

End Sub

answered Nov 21, 2019 at 11:27

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Object Variable or With Block Not Set- Userform

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related