Help needed with FIND routine.
c = Nothing
raises an error.
You need set to assign a reference to an object - in this case you are
setting a reference to nothing - in otherwords, the object variable does not
have a reference
If you want to test for that condition
If c is nothing then
--
Regards,
Tom Ogilvy
Matthew wrote in message
...
Tom, thanks!
A couple of tweaks to suit my system and it works a
treat, I was stuck on the selecting multiple sheets for
my range and also returing the row reference for the
found items.
I am still a little confused about the Set command. What
is the difference between:
set c = Nothing
and
c = Nothing
Thanks again
Matthew
-----Original Message-----
so just to add to the example:
Dim sStr as String
Dim c as Range
Dim firstAddress as String
sStr = "ABCD"
for each ws in Worksheets(Array
("Sheet1","Sheet2","Sheet3"))
firstAddress = ""
set c = Nothing
With Ws.Cells
Set c = .Find(sStr, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
application.Goto Reference:=c, Scroll:=True
msgbox "found at " & c.row & " on Sheet: " &
ws.Name
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With
Next ws
--
Regards,
Tom Ogilvy
Don Guillett wrote in message
...
It's amazing what you can find in HELP.
FindNext Method
See Also Applies To Example Specifics
Continues a search that was begun with the Find
method. Finds the next
cell
that matches those same conditions and returns a Range
object that
represents that cell. Doesn't affect the selection or
the active cell.
expression.FindNext(After)
expression Required. An expression that returns a
Range object.
After Optional Variant. The cell after which you
want to search. This
corresponds to the position of the active cell when a
search is done from
the user interface. Note that After must be a single
cell in the range.
Remember that the search begins after this cell; the
specified cell isn't
searched until the method wraps back around to this
cell. If this argument
isn't specified, the search starts after the cell in
the upper-left corner
of the range.
Remarks
When the search reaches the end of the specified
search range, it wraps
around to the beginning of the range. To stop a search
when this
wraparound
occurs, save the address of the first found cell, and
then test each
successive found-cell address against this saved
address.
Example
This example finds all cells in the range A1:A500 that
contain the value 2
and changes their values to 5.
With Worksheets(1).Range("a1:a500")
Set c = .Find(2, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = 5
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <
firstAddress
End If
End With"Matthew" wrote in message
...
Dear All,
I'm a newbie to VBA, running Excel 2000.
I have got myself very confused with a find routine
that
I am trying to write. I take a user input, a string,
then I want to be able to look on three different
worksheets to find the value and return the row
number,
of where the found item is. I then want to be able
to
repeat the routine, as desired with a find next type
command. Advancing to the next worksheet once one
has
been searched.
Can anybody please supply me with some sameple code
to
work with.
TIA
Matthew
.
|