Help needed with FIND routine.
 

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

Help needed with FIND routine.
 

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

Help needed with FIND routine.
 

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

Help needed with FIND routine.
 

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




.

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




.