?
Solved

looking for a match comparing field to value

Posted on 2011-10-30
13
Medium Priority
?
214 Views
Last Modified: 2012-08-13
excel 2003
access 2003
vba

is it possible to turn this code into a "Like" wildcard match ?

' This works
If CStr(rng.Offset(0, -1).Value) = rs.Fields(1) Then
 .Range("A" & j).Offset(0, 52) = "Found"
 End If

This does not work:
If CStr(rng.Offset(0, -1).Value) Like "*' &  rs.Fields(1) & '*"  Then
 .Range("A" & j).Offset(0, 52) = "FoundSomething"
 End If


Thanks
fordraiders
0
Comment
Question by:Fordraiders
  • 6
  • 6
13 Comments
 
LVL 16

Accepted Solution

by:
Peter Kwan earned 2000 total points
ID: 37052072
You may use instr method

If Instr(rng.Offset(0, -1).Value, rs.Fields(1)) > 0 then ...
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 37052080
perfect Thanks
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37052084
Is there a syntax error with your single quote?  Shouldn't it be:

If CStr(rng.Offset(0, -1).Value) Like "*" &  rs.Fields(1) & "*"  Then

It should work just fine with that corrected.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Expert Comment

by:dlmille
ID: 37052085
Ok then, when you want a wildcard match, don't give up - just ensure the syntax is correct ;)

Dave
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 37052096
one quirk

rng.Offset(0, -1).Value =  "PROTO"

YOUR CODE will not find  "PROTO" if its at the end of the string ?
If Instr(rng.Offset(0, -1).Value, rs.Fields(1)) > 0

if the field rs.fields(1) =  "BLACKHAWK BY PROTO"

0
 
LVL 3

Author Comment

by:Fordraiders
ID: 37052101
dlmile, would not work...sorry
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37052102
"BLACKHAWK BY PROTO" like "*PROTO*"  works - your wildcards will work if you use the correct syntax - see my post.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37052108
fordraiders - certainly does work.  Just use the LIKE properly correctly.

Here's the proper use from Excel Help:

  Like Operator




Used to compare two strings.

Syntax

result = string Like pattern

Consult Excel Help for the GUI version of how LIKE works.

Its STRING like PATTERN, not PATTERN LIKE STRING.

Cheers,

Dave
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 37052112
dave,
If CStr(rng.Offset(0, -1).Value) Like "*" & rs.Fields(1) & "*" Then
  .Range("A" & j).Offset(0, 53) = "NAME MATCH"
 End If

it will not find "PROTO"  CStr(rng.Offset(0, -1).Value)    inside        "BLACKHAWK BY PROTO" "*" & rs.Fields(1) & "*"
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37052147
You're doing it incorrectly.

If "PROTO" is in rng.offset(0,-1).value

and "BLACKHAWK BY PROTO" is in rs.Fields(1)

Then the correct syntax is STRING LIKE PATTERN

If rs.Fields(1) LIKE "*" & CStr(rng.Offset(0,-1).Value) & "*" Then
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 37052422
dave. Thanks for staying with me...ok I see what you mean now...
ok now how do you get some points ?
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37052428
No need.

by the way - you might be doing the INSTR function backwards as well, since you noted it didn't work on another example, unless I missread the post - just make sure the STRING is the first parameter.

Also, suggest waiting a few :) minutes before accepting solutions especially if they come from a tangent (re: you were given a perfectly good solution, however, it didn't explain why you were having a problem with LIKE), as several Experts might be banging away competing solutions and you can have your pick, reward the first, or reward them all.

Cheers,

Dave
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 37052431
Thanks, again...very much ! and yes I will wait next time'
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question