Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
Private Sub CommandButton2_Click()
Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
Excel 2003
Finny wrote: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
First, if you're using XL97 and a CommandButton from the Command
Toolbox, you need to set the takefocusonclick property to false. Otherwise, the line is trying to activate a range that is set to Nothing. If Find doesn't find strItem in rngItems (remember that find may be case sensitive, since you didn't set MatchCase, or, perhaps, you have leading or trailing whitespace characters), then trying to Activate Nothing will cause the "Object variable...not set" error. Try: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range, _ rngFound As Range Set rngItems = Range("C:C") strItem = Range("A1").Value 'Sheets("forecast").Select Set rngFound = rngItems.Find(What:=strItem) If Not rngFound Is Nothing Then rngFound.Activate Else MsgBox strItem & " not found." End If End Sub It probably wouldn't work as written anyway, unless Commandbutton2 is on sheet "forecast" (in which case there's no reason to select "forecast"). You can't activate a cell on a non-active sheet. In article .com, "Finny" wrote: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
If the value isn't found, then the foundcell can't be activated.
And I'd be more explicit with the ranges: Private Sub CommandButton2_Click() 'just because I like dimming my variables one per line! Dim strItem As String Dim rngItems As Range Dim FoundCell as Range strItem = me.Range("A1").Value with worksheets("forecast") Set rngItems = .Range("C:C") set foundcell = rngitems.find(what:=stritem) end with if foundcell is nothing then msgbox stritem & " wasn't found" else application.goto foundcell ', scroll:=true '??? end if End Sub And be careful. With code in a general module, unqualified ranges belong to the activesheet. In code behind the worksheet (where this is???), then an unqualified range belongs to the worksheet holding the code. And you can only select a cell on the active sheet. Finny wrote: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
Any time you use find you have to wory about not finding what you are looking
for. Also find uses the settings selected the last time find was used so you need to specify all of the settings (almost always). Finally you specify a range in which too look on the currently active sheet but you change your sheet prior to doing the select which will not work. You can not select a range on a sheet that is not active... dim rngFound as range dim rngToSearch as range dim wksThisSheet as worksheet dim wksForecast as worksheet dim strItem as string set wksThisSheet = activesheet set wksForecast = sheets("forecast") set rngToSearch = wksThisSheet.columns("C") strItem = wksThisSheet.Range("A1").Value set rngfound = rngtosearch.find(What:=strItem, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) if rngfound is nothing then msgbox "Sorry, Not Found..." else wksThisSheet.select rngfound.Select end if -- HTH... Jim Thomlinson "Finny" wrote: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
Thanks all.
I'll look into this tomorrow. With the exact string I can find it manually no problem. In fact the code was originally recorded. Does error 91 really have anything to do with not finding the item (even though it should) Thanks again. Jim Thomlinson wrote: Any time you use find you have to wory about not finding what you are looking for. Also find uses the settings selected the last time find was used so you need to specify all of the settings (almost always). Finally you specify a range in which too look on the currently active sheet but you change your sheet prior to doing the select which will not work. You can not select a range on a sheet that is not active... dim rngFound as range dim rngToSearch as range dim wksThisSheet as worksheet dim wksForecast as worksheet dim strItem as string set wksThisSheet = activesheet set wksForecast = sheets("forecast") set rngToSearch = wksThisSheet.columns("C") strItem = wksThisSheet.Range("A1").Value set rngfound = rngtosearch.find(What:=strItem, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) if rngfound is nothing then msgbox "Sorry, Not Found..." else wksThisSheet.select rngfound.Select end if -- HTH... Jim Thomlinson "Finny" wrote: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
Okay I should have mentioned.
The button is on a different sheet than forecast. But aren't I activating the forecast sheet with sheet.select? later.. Jim Thomlinson wrote: Any time you use find you have to wory about not finding what you are looking for. Also find uses the settings selected the last time find was used so you need to specify all of the settings (almost always). Finally you specify a range in which too look on the currently active sheet but you change your sheet prior to doing the select which will not work. You can not select a range on a sheet that is not active... dim rngFound as range dim rngToSearch as range dim wksThisSheet as worksheet dim wksForecast as worksheet dim strItem as string set wksThisSheet = activesheet set wksForecast = sheets("forecast") set rngToSearch = wksThisSheet.columns("C") strItem = wksThisSheet.Range("A1").Value set rngfound = rngtosearch.find(What:=strItem, _ LookAt:=xlWhole, _ LookIn:=xlFormulas, _ MatchCase:=True) if rngfound is nothing then msgbox "Sorry, Not Found..." else wksThisSheet.select rngfound.Select end if -- HTH... Jim Thomlinson "Finny" wrote: Private Sub CommandButton2_Click() Dim strItem As String, _ rngItems As Range Set rngItems = Range("C:C") strItem = Range("A1").Value Sheets("forecast").Select rngItems.Find(What:=strItem).Activate End Sub I keep getting Object variable or With block variable not set (Error 91) at the last line (.Find) any help appreciated |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this code??
If you don't qualify a range with the worksheet it belongs to, then the
active sheet is assumed, so Set rngItems = Range("C:C") is equivalent to Set rngItems = ActiveSheet.Range("C:C") If you want rngItems to be column C of sheet "forecast" then use Set rngItems = Sheets("forecast").Range("C:C") strItem = Range("A1").Value 'Assuming you want A1 of active sheet Sheets("forecast").Select rngItems.Find(...).Activate or, perhaps better: strItem = ActiveSheet.Range("A1").Value With Sheets("forecast") .Activate Set rFound = .Range("C:C").Find(What:=strItem) If Not rFound Is Nothing Then rFound.Activate Else MsgBox strItem & " was not found" End If End With In article .com, "Finny" wrote: Okay I should have mentioned. The button is on a different sheet than forecast. But aren't I activating the forecast sheet with sheet.select? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
What the F@#% is wrong with this FIND code??
Thanks JE that worked like a charm!
And thanks all for the replies. I guess I need to better understand how activation works. Cheers! P.S. sorry for the generic if rude subject line JE McGimpsey wrote: If you don't qualify a range with the worksheet it belongs to, then the active sheet is assumed, so Set rngItems = Range("C:C") is equivalent to Set rngItems = ActiveSheet.Range("C:C") If you want rngItems to be column C of sheet "forecast" then use Set rngItems = Sheets("forecast").Range("C:C") strItem = Range("A1").Value 'Assuming you want A1 of active sheet Sheets("forecast").Select rngItems.Find(...).Activate or, perhaps better: strItem = ActiveSheet.Range("A1").Value With Sheets("forecast") .Activate Set rFound = .Range("C:C").Find(What:=strItem) If Not rFound Is Nothing Then rFound.Activate Else MsgBox strItem & " was not found" End If End With In article .com, "Finny" wrote: Okay I should have mentioned. The button is on a different sheet than forecast. But aren't I activating the forecast sheet with sheet.select? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what is wrong with this code? | Excel Programming | |||
What's wrong with my code ? | Excel Worksheet Functions | |||
What is wrong with this code? | Excel Programming | |||
What's wrong with this code, please? | Excel Programming | |||
What is wrong with this code? | Excel Programming |