Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91 -- Object variable or With block variable not set
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91 -- Object variable or With block variable not set
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? Rachael wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error 91 -- Object variable or With block variable not set
You get that error if the search is not successful. Is Sheet1 of the
activeworkbook blank? -- Regards, Tom Ogilvy "Rachael" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error '91' Object variable or With block variable not set | Excel Discussion (Misc queries) | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
QueryTables Object Variable or With Block Variable Not Set | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming | |||
Error 91 - Object variable with block variable not set | Excel Programming |