What the F@#% is wrong with this code??

[Finny]

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

 

[Finny]

Excel 2003

 

[JE McGimpsey]

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.

 

[Dave Peterson]

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.

 

[Guest]

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

 

[Finny]

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.

 

[Finny]

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..

 

[JE McGimpsey]

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

 

[Finny]

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