Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a string match in a column
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" 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a string match in a column
Try using
res = Application.Match(myVar, MyRng,0) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Matilda" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a string match in a column
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" 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a string match in a column
res should be declared as a Variant.
-- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "Matilda" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a string match in a column
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" 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup to find a match in a string | Excel Worksheet Functions | |||
How to find last match in a column? | Excel Discussion (Misc queries) | |||
Find a string within a column | Excel Discussion (Misc queries) | |||
Find text string in column? | Excel Discussion (Misc queries) | |||
Trying to find string in a column or row | Excel Programming |