VBA searching a row
 

Howdy,
I want to search a row, say from B2 to O2, for a cell containing an s and
get the position of that cell, preferably in a format of number of cells
from the left. I've looked into using .Find, but I haven't been able to
figure out a way to get the information that I want. Could any of you point
me to an example involving this scenario, or give me some pointers?

Thanks a bunch,
Fargo

VBA searching a row
 

Fargo,

the find method returns a range object, and you can then
test the address property directly to get the info you
want.

The following simple code finds the first cell in your
range that contains an "s" and outputs the address to the
immediate window.

Sub FindTheS()

Dim SLoc As Range

Set SLoc = Sheets("Sheet1").Range("A15:M15").Find _
(what:="s", LookIn:=xlValues)

Debug.Print SLoc.Address

End Sub

Merry Christmas, Pete

-----Original Message-----
Howdy,
I want to search a row, say from B2 to O2, for a cell
containing an s and
get the position of that cell, preferably in a format of
number of cells
from the left. I've looked into using .Find, but I
haven't been able to
figure out a way to get the information that I want.
Could any of you point
me to an example involving this scenario, or give me some
pointers?

Thanks a bunch,
Fargo

VBA searching a row
 

Sub findscol()
x = Range("b2:o2").Find("s").Column - 1
MsgBox x
End Sub

I don't quite understand the "from the left" but this will find the column
and subtract col A.
Col O is 15 so you could use
15-Range("b2:o2").Find("s").Column
to get 10


--
Don Guillett
SalesAid Software

"Fargo Holiday" wrote in message
news:06cGb.443084$Dw6.1350369@attbi_s02...
Howdy,
I want to search a row, say from B2 to O2, for a cell containing an s and
get the position of that cell, preferably in a format of number of cells
from the left. I've looked into using .Find, but I haven't been able to
figure out a way to get the information that I want. Could any of you
point
me to an example involving this scenario, or give me some pointers?

Thanks a bunch,
Fargo

VBA searching a row
 

Dim rng as Range
Dim res as Variant
set rng = Range("B2:O2")
res = Application.Match("s",rng,0)
if not iserror(res) then
msgbox "s found at cell number " & res & " in range " & _
rng.Address(0,0) & " cell address is: " & rng(1,res).Address
Else
msgbox "s not found"
End If

--
Regards,
Tom Ogilvy

"Fargo Holiday" wrote in message
news:06cGb.443084$Dw6.1350369@attbi_s02...
Howdy,
I want to search a row, say from B2 to O2, for a cell containing an s and
get the position of that cell, preferably in a format of number of cells
from the left. I've looked into using .Find, but I haven't been able to
figure out a way to get the information that I want. Could any of you
point
me to an example involving this scenario, or give me some pointers?

Thanks a bunch,
Fargo

VBA searching a row
 

Don Guillet's solution will find a cell containing an s; Tom Ogilvy's
will find a cell containing only an s.

Alan Beban

Fargo Holiday wrote:
Howdy,
I want to search a row, say from B2 to O2, for a cell containing an s and
get the position of that cell, preferably in a format of number of cells
from the left. I've looked into using .Find, but I haven't been able to
figure out a way to get the information that I want. Could any of you point
me to an example involving this scenario, or give me some pointers?

Thanks a bunch,
Fargo

VBA searching a row
 

If looking for s as a substring

Dim rng as Range
Dim res as Variant
set rng = Range("B2:O2")
res = Application.Match("*s*",rng,0)
if not iserror(res) then
msgbox "s found at cell number " & res & " in range " & _
rng.Address(0,0) & " cell address is: " & rng(1,res).Address
Else
msgbox "s not found"
End If

Don Guillet's solution will find a cell containing an s;
This is actually incorrect. Don's approach is modelled on code often posted
by Alan as well (intentional or not) which shares the same uncertaintly.

We actually don't know whether Don's code would find a cell containing s as
a substring since he hasn't bothered to set the
LookAt:=
parameter which may be set to xlwhole or xlpart.

Since this is a persistent setting, it would depend on what was done the
last time it was set - clearly a bad approach.


Quote from help for the save method:
"The settings for LookIn, LookAt, SearchOrder, MatchCase, and MatchByte are
saved each time you use this method. If you don't specify values for these
arguments the next time you call the method, the saved values are used. "


Also, the phrase "containing an s" is ambiguous - it could mean only an s or
s as part of a larger string. My original interpretation was that it was
only containing an s.

Thanks for raising the issue.

--
Regards,
Tom Ogilvy


Alan Beban wrote in message
...
Don Guillet's solution will find a cell containing an s; Tom Ogilvy's
will find a cell containing only an s.

Alan Beban

Fargo Holiday wrote:
Howdy,
I want to search a row, say from B2 to O2, for a cell containing an s
and
get the position of that cell, preferably in a format of number of cells
from the left. I've looked into using .Find, but I haven't been able to
figure out a way to get the information that I want. Could any of you
point
me to an example involving this scenario, or give me some pointers?

Thanks a bunch,
Fargo