VBA searching a row

  • Thread starter Thread starter Fargo Holiday
  • Start date Start date
F

Fargo Holiday

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
 
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
 
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
 
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; Tom Ogilvy's
will find a cell containing only an s.

Alan Beban
 
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;
Click to expand...
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.
 
Back
Top