Find a string match in a column

Hi,

I am wanting to find a string from a column which matches my variable. I
modified this code of Tom Ogilvy's in a related answer

For Each cell In rngA
res = Application.Match("*" & cell.Value & "*", rngB, 0)
If Not IsError(res) Then
Set rng = rngB(res)
rng.Offset(0, 1).Value = cell
End If

to

For Each cell In rngA
res = Application.Match(myVar, MyRng)
If Not IsError(res) Then
myCode
End If
next

but I get a number in res that in fact is a blank cell. Can't work out what
is happening. Any help appreciated.

You omitted the third argument in the function.
Add comma, zero after MyRng
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Matilda"
<[email protected]>
wrote in message
Hi,
I am wanting to find a string from a column which matches my variable. I
modified this code of Tom Ogilvy's in a related answer

For Each cell In rngA
res = Application.Match("*" & cell.Value & "*", rngB, 0)
If Not IsError(res) Then
Set rng = rngB(res)
rng.Offset(0, 1).Value = cell
End If

to

For Each cell In rngA
res = Application.Match(myVar, MyRng)
If Not IsError(res) Then
myCode
End If
next

but I get a number in res that in fact is a blank cell. Can't work out what
is happening. Any help appreciated.

You omitted the third argument in the function.
Add comma, zero after MyRng
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Matilda"
<[email protected]>
wrote in message
Hi,
I am wanting to find a string from a column which matches my variable. I
modified this code of Tom Ogilvy's in a related answer

For Each cell In rngA
res = Application.Match("*" & cell.Value & "*", rngB, 0)
If Not IsError(res) Then
Set rng = rngB(res)
rng.Offset(0, 1).Value = cell
End If

to

For Each cell In rngA
res = Application.Match(myVar, MyRng)
If Not IsError(res) Then
myCode
End If
next

but I get a number in res that in fact is a blank cell. Can't work out what
is happening. Any help appreciated.

Try using

res = Application.Match(myVar, MyRng,0)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Matilda" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am wanting to find a string from a column which matches my variable. I
> modified this code of Tom Ogilvy's in a related answer
>
> For Each cell In rngA
> res = Application.Match("*" & cell.Value & "*", rngB, 0)
> If Not IsError(res) Then
> Set rng = rngB(res)
> rng.Offset(0, 1).Value = cell
> End If
>
> to
>
> For Each cell In rngA
> res = Application.Match(myVar, MyRng)
> If Not IsError(res) Then
> myCode
> End If
> next
>
> but I get a number in res that in fact is a blank cell. Can't work out
what
> is happening. Any help appreciated.

Hi Jim,

I tried that, but get a type mismatch error. I'm obviously on the wrong
wavelength here. I assumed that Match would return a number (row number) if
the cell being examined contained a string maatching the variable. The column
being searched contains strings, not dates or numbers, so can't work out what
is happening.

Many thanks

"Jim Cone" wrote:

> You omitted the third argument in the function.
> Add comma, zero after MyRng
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Matilda"
> <[email protected]>
> wrote in message
> Hi,
> I am wanting to find a string from a column which matches my variable. I
> modified this code of Tom Ogilvy's in a related answer
>
> For Each cell In rngA
> res = Application.Match("*" & cell.Value & "*", rngB, 0)
> If Not IsError(res) Then
> Set rng = rngB(res)
> rng.Offset(0, 1).Value = cell
> End If
>
> to
>
> For Each cell In rngA
> res = Application.Match(myVar, MyRng)
> If Not IsError(res) Then
> myCode
> End If
> next
>
> but I get a number in res that in fact is a blank cell. Can't work out what
> is happening. Any help appreciated.
>

res should be declared as a Variant.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html

"Matilda"
<[email protected]>
wrote in message
Hi Jim,
I tried that, but get a type mismatch error. I'm obviously on the wrong
wavelength here. I assumed that Match would return a number (row number) if
the cell being examined contained a string maatching the variable. The column
being searched contains strings, not dates or numbers, so can't work out what
is happening.
Many thanks

"Jim Cone" wrote:

> You omitted the third argument in the function.
> Add comma, zero after MyRng
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware

aaaaahhhhhh !!!! Thanks Bob, and Jim. Variant type ... sorted!



"Bob Phillips" wrote:

> Try using
>
> res = Application.Match(myVar, MyRng,0)
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Matilda" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I am wanting to find a string from a column which matches my variable. I
> > modified this code of Tom Ogilvy's in a related answer
> >
> > For Each cell In rngA
> > res = Application.Match("*" & cell.Value & "*", rngB, 0)
> > If Not IsError(res) Then
> > Set rng = rngB(res)
> > rng.Offset(0, 1).Value = cell
> > End If
> >
> > to
> >
> > For Each cell In rngA
> > res = Application.Match(myVar, MyRng)
> > If Not IsError(res) Then
> > myCode
> > End If
> > next
> >
> > but I get a number in res that in fact is a blank cell. Can't work out
> what
> > is happening. Any help appreciated.
>
>
>