Error 91 -- Object variable or With block variable not set

[Rachael]

hello there.. I have this userform which was running well on excel xp
professional. However i started getting error 91 when i run it on
excel 2000. Will anyone help me with that? thanks
this is the error line: LR = .Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

--------------------------------------------------------------------------------
Private Sub enter_Click()
Dim LR As Long, DestSheet As Worksheet, response As Integer
Set DestSheet = Sheets("Sheet1")
With DestSheet
LR = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row + 1

..Cells(LR, 1).Value = TextBox1.Text
..Cells(LR, 2).Value = TextBox2.Text
..Cells(LR, 3).Value = TextBox3.Text
..Cells(LR, 4).Value = TextBox4.Text
..Cells(LR, 5).Value = TextBox5.Text
..Cells(LR, 6).Value = TextBox6.Text
..Cells(LR, 7).Value = TextBox7.Text
..Cells(LR, 8).Value = TextBox8.Text
..Cells(LR, 9).Value = TextBox9.Text
..Cells(LR, 10).Value = TextBox10.Text
End With

MsgBox "One record written to " & DestSheet.Name & " on row " & LR &
".", 64, "FYI..."

response = MsgBox("Do you want to enter another record?", vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = Date
TextBox9.Text = ""
TextBox10.Text = ""
TextBox1.SetFocus

Else
Set DestSheet = Nothing
Unload Me
End If
End Sub

 

[Dave Peterson]

First, change this [A1] to .range("a1") or .[A1]

(the dots mean that it refers to the previous With statement.)

I find it easier to read and [A1] will refer to A1 on the activesheet--not
necessarily sheet1 (destSheet). (It's also quicker--not too much of a problem
in just a single instance, though.)

And your code worked as-is for me--as long as there was something on Sheet1. If
there was nothing on sheet1, then the .find failed, so .row+1 returned an error.

Do you have stuff on that sheet in your testing?

hello there.. I have this userform which was running well on excel xp
professional. However i started getting error 91 when i run it on
excel 2000. Will anyone help me with that? thanks
this is the error line: LR = .Cells.Find(What:="*", After:=[A1],
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

--------------------------------------------------------------------------------
Private Sub enter_Click()
Dim LR As Long, DestSheet As Worksheet, response As Integer
Set DestSheet = Sheets("Sheet1")
With DestSheet
LR = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows,
SearchDirection:=xlPrevious).Row + 1

.Cells(LR, 1).Value = TextBox1.Text
.Cells(LR, 2).Value = TextBox2.Text
.Cells(LR, 3).Value = TextBox3.Text
.Cells(LR, 4).Value = TextBox4.Text
.Cells(LR, 5).Value = TextBox5.Text
.Cells(LR, 6).Value = TextBox6.Text
.Cells(LR, 7).Value = TextBox7.Text
.Cells(LR, 8).Value = TextBox8.Text
.Cells(LR, 9).Value = TextBox9.Text
.Cells(LR, 10).Value = TextBox10.Text
End With

MsgBox "One record written to " & DestSheet.Name & " on row " & LR &
".", 64, "FYI..."

response = MsgBox("Do you want to enter another record?", vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = Date
TextBox9.Text = ""
TextBox10.Text = ""
TextBox1.SetFocus

Else
Set DestSheet = Nothing
Unload Me
End If
End Sub

 

[Tom Ogilvy]

You get that error if the search is not successful. Is Sheet1 of the
activeworkbook blank?

 

[rach85]

thanks tom..got it fixed. my spreadsheet was empty.. so results in thi
erro