Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
I use the following code below to populate the current cell on the January
worksheet (there is a worksheet for each month, and each month has to have all the names that appear on the ParaTransit Names worksheet) with the name that is entered in the referenced cell on the ParaTransit Names worksheet: =IF('ParaTransit Names'!B15"",'ParaTransit Names'!B15,"") I then use the following code to place the name contained in the active cell on the January worksheet in the TextBox of the UserForm: Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) 'Places date in box at top of userform TextBox1.Text = rng(1, 2).Text End Sub What I was hoping for, is that when I ran the code below it would search out and select the cell matching the TextBox value on the ParaTransit Names worksheet. What I get is an Error Msg "object variable or With block variable not set". Any guidance here? Private Sub CommandButton1_Click() With ActiveWorkbook.Worksheets("ParaTransit Names") Cells.Find(What:=Me.TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
A cell doesn't have TEXT it should be VALUE.
TextBox1.Text = rng(1, 2).Value "Patrick C. Simonds" wrote: I use the following code below to populate the current cell on the January worksheet (there is a worksheet for each month, and each month has to have all the names that appear on the ParaTransit Names worksheet) with the name that is entered in the referenced cell on the ParaTransit Names worksheet: =IF('ParaTransit Names'!B15"",'ParaTransit Names'!B15,"") I then use the following code to place the name contained in the active cell on the January worksheet in the TextBox of the UserForm: Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) 'Places date in box at top of userform TextBox1.Text = rng(1, 2).Text End Sub What I was hoping for, is that when I ran the code below it would search out and select the cell matching the TextBox value on the ParaTransit Names worksheet. What I get is an Error Msg "object variable or With block variable not set". Any guidance here? Private Sub CommandButton1_Click() With ActiveWorkbook.Worksheets("ParaTransit Names") Cells.Find(What:=Me.TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
Thanks, I changed that, but it still does not solve the problem.
"Joel" wrote in message ... A cell doesn't have TEXT it should be VALUE. TextBox1.Text = rng(1, 2).Value "Patrick C. Simonds" wrote: I use the following code below to populate the current cell on the January worksheet (there is a worksheet for each month, and each month has to have all the names that appear on the ParaTransit Names worksheet) with the name that is entered in the referenced cell on the ParaTransit Names worksheet: =IF('ParaTransit Names'!B15"",'ParaTransit Names'!B15,"") I then use the following code to place the name contained in the active cell on the January worksheet in the TextBox of the UserForm: Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) 'Places date in box at top of userform TextBox1.Text = rng(1, 2).Text End Sub What I was hoping for, is that when I ran the code below it would search out and select the cell matching the TextBox value on the ParaTransit Names worksheet. What I get is an Error Msg "object variable or With block variable not set". Any guidance here? Private Sub CommandButton1_Click() With ActiveWorkbook.Worksheets("ParaTransit Names") Cells.Find(What:=Me.TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
Private Sub CommandButton1_Click() ThisWorkbook.Sheets("paratransit names").Activate Cells.Find(What:=TextBox1.Text, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate End Sub Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Text = rng.Value End Sub Cliff Edwards |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
Try this
Private Sub cmdOK_Click() Dim rng As Range With ActiveSheet 'ActiveWorkbook.Worksheets("ParaTransit Names") Set rng = Cells.Find(What:=CDate(Me.TextBox1.Text), _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) If Not rng Is Nothing Then rng.Activate End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Patrick C. Simonds" wrote in message ... I use the following code below to populate the current cell on the January worksheet (there is a worksheet for each month, and each month has to have all the names that appear on the ParaTransit Names worksheet) with the name that is entered in the referenced cell on the ParaTransit Names worksheet: =IF('ParaTransit Names'!B15"",'ParaTransit Names'!B15,"") I then use the following code to place the name contained in the active cell on the January worksheet in the TextBox of the UserForm: Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) 'Places date in box at top of userform TextBox1.Text = rng(1, 2).Text End Sub What I was hoping for, is that when I ran the code below it would search out and select the cell matching the TextBox value on the ParaTransit Names worksheet. What I get is an Error Msg "object variable or With block variable not set". Any guidance here? Private Sub CommandButton1_Click() With ActiveWorkbook.Worksheets("ParaTransit Names") Cells.Find(What:=Me.TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
Of course it does, it has Value, Value2 and Text. Check you help.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Joel" wrote in message ... A cell doesn't have TEXT it should be VALUE. TextBox1.Text = rng(1, 2).Value "Patrick C. Simonds" wrote: I use the following code below to populate the current cell on the January worksheet (there is a worksheet for each month, and each month has to have all the names that appear on the ParaTransit Names worksheet) with the name that is entered in the referenced cell on the ParaTransit Names worksheet: =IF('ParaTransit Names'!B15"",'ParaTransit Names'!B15,"") I then use the following code to place the name contained in the active cell on the January worksheet in the TextBox of the UserForm: Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) 'Places date in box at top of userform TextBox1.Text = rng(1, 2).Text End Sub What I was hoping for, is that when I ran the code below it would search out and select the cell matching the TextBox value on the ParaTransit Names worksheet. What I get is an Error Msg "object variable or With block variable not set". Any guidance here? Private Sub CommandButton1_Click() With ActiveWorkbook.Worksheets("ParaTransit Names") Cells.Find(What:=Me.TextBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, SearchFormat:=False).Activate End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
I get the same error msg.
I case it was not clear as to what I am trying to do, I want this code to run without actually making the worksheet "paratransit names" active. The code below does what I need to the extent that it finds the text, but at the expense of making the "paratransit names" worksheet active. Maybe it is not possible to perform this function without making the worksheet active. If that is the case, I need something to bring me back to the cell on the worksheet which was active when the code was initiated. Private Sub CommandButton1_Click() ThisWorkbook.Sheets("paratransit names").Activate Cells.Find(What:=TextBox1.Text, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Unload UserForm1 End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
I couldn't find a way to use find without activating the sheet, just
store your active sheet/cell in variables before the find and return to them after. Private Sub CommandButton1_Click() Dim aSht As Worksheet Dim aCel As Range Set aSht = ActiveSheet Set aCel = ActiveCell ThisWorkbook.Sheets("paratransit names").Activate Cells.Find(What:=TextBox1.Text, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate aSht.Activate aCel.Select Unload Me End Sub Private Sub UserForm_Initialize() Dim rng Set rng = Cells(ActiveCell.Row, 1) TextBox1.Text = rng.Value End Sub Cliff Edwards |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
Your commandbutton1 is a commandbutton from the Control toolbox toolbar placed
on a worksheet, right? If that's true, then your unqualified ranges (like cells in cells.find) will refer to the sheet that owns the code--not necessarily the activesheet. Option Explicit Private Sub CommandButton1_Click() dim FoundCell as range with ThisWorkbook.Sheets("paratransit names") set foundcell = .Cells.Find(What:=me.TextBox1.Text, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) end with if foundcell is nothing then 'what happens else application.goto foundcell end if End Sub "Patrick C. Simonds" wrote: I get the same error msg. I case it was not clear as to what I am trying to do, I want this code to run without actually making the worksheet "paratransit names" active. The code below does what I need to the extent that it finds the text, but at the expense of making the "paratransit names" worksheet active. Maybe it is not possible to perform this function without making the worksheet active. If that is the case, I need something to bring me back to the cell on the worksheet which was active when the code was initiated. Private Sub CommandButton1_Click() ThisWorkbook.Sheets("paratransit names").Activate Cells.Find(What:=TextBox1.Text, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Unload UserForm1 End Sub -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
No, sorry it is on a UserForm
"Dave Peterson" wrote in message ... Your commandbutton1 is a commandbutton from the Control toolbox toolbar placed on a worksheet, right? If that's true, then your unqualified ranges (like cells in cells.find) will refer to the sheet that owns the code--not necessarily the activesheet. Option Explicit Private Sub CommandButton1_Click() dim FoundCell as range with ThisWorkbook.Sheets("paratransit names") set foundcell = .Cells.Find(What:=me.TextBox1.Text, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) end with if foundcell is nothing then 'what happens else application.goto foundcell end if End Sub "Patrick C. Simonds" wrote: I get the same error msg. I case it was not clear as to what I am trying to do, I want this code to run without actually making the worksheet "paratransit names" active. The code below does what I need to the extent that it finds the text, but at the expense of making the "paratransit names" worksheet active. Maybe it is not possible to perform this function without making the worksheet active. If that is the case, I need something to bring me back to the cell on the worksheet which was active when the code was initiated. Private Sub CommandButton1_Click() ThisWorkbook.Sheets("paratransit names").Activate Cells.Find(What:=TextBox1.Text, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Unload UserForm1 End Sub -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object Variable error
I would still use the same code (with the "unload me" line added).
You can't go wrong by qualifying your ranges. "Patrick C. Simonds" wrote: No, sorry it is on a UserForm "Dave Peterson" wrote in message ... Your commandbutton1 is a commandbutton from the Control toolbox toolbar placed on a worksheet, right? If that's true, then your unqualified ranges (like cells in cells.find) will refer to the sheet that owns the code--not necessarily the activesheet. Option Explicit Private Sub CommandButton1_Click() dim FoundCell as range with ThisWorkbook.Sheets("paratransit names") set foundcell = .Cells.Find(What:=me.TextBox1.Text, _ After:=.cells(.cells.count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) end with if foundcell is nothing then 'what happens else application.goto foundcell end if End Sub "Patrick C. Simonds" wrote: I get the same error msg. I case it was not clear as to what I am trying to do, I want this code to run without actually making the worksheet "paratransit names" active. The code below does what I need to the extent that it finds the text, but at the expense of making the "paratransit names" worksheet active. Maybe it is not possible to perform this function without making the worksheet active. If that is the case, I need something to bring me back to the cell on the worksheet which was active when the code was initiated. Private Sub CommandButton1_Click() ThisWorkbook.Sheets("paratransit names").Activate Cells.Find(What:=TextBox1.Text, _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Unload UserForm1 End Sub -- Dave Peterson -- Dave Peterson |
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) | |||
Runtime Error 91 Object variable or With block variable not set. | Excel Programming | |||
Run-Time error '91': Object variable of With block variable not set | Excel Programming | |||
Run-time error '91': "Object variable or With block variable not set | Excel Programming | |||
Cells.Find error Object variable or With block variable not set | Excel Programming |