Match

  • Thread starter Thread starter fi.or.jp.de
  • Start date Start date
F

fi.or.jp.de

Hi All,

i have value B_12-23

it should match with
B_12-23
B_12 23
B 12 23
B1223

I need vba solution.

Thanks in advance.
 
Hi

Maybe this is what you need:

Sub MyMatch()
Set TargetRange = Range("A1:A4")
Set MatchCell = Range("B1")
MatchValue = WorksheetFunction.Match(MatchCell, TargetRange, 0)
MsgBox ("Match is : " & MatchValue)
End Sub

Regards,
Per
 
As I am sure there are other letters and numbers besides "B", "12, and "23"
that you will need to match, then let's call your B_12-23 value Code and
your other possible values TextVal; then your test would be this...

If Replace(Replace(Code, "-", ""), "_", "") = _
Replace(Replace(TextVal, "-", ""), "_", "") Then
MsgBox "They match"
Else
MsgBox "They do not match"
End If

Know though that this is not a foolproof test as TextVal's like B-1_2-2_3
would also show up as matching the Code value B_12-23. Only you know if this
is a "good" enough test as only you know the possible Code and TextVal
values that can be in your data. Other more exacting tests might be
possible, but you would have to tell us about any restraints regarding the
"shape" that the Code and TextVal's could possibly take.
 
Thanks Rick,

I was waiting on my last post.



As I am sure there are other letters and numbers besides "B", "12, and "23"
that you will need to match, then let's call your B_12-23 value Code and
your other possible values TextVal; then your test would be this...

If Replace(Replace(Code, "-", ""), "_", "") = _
     Replace(Replace(TextVal, "-", ""), "_", "")  Then
  MsgBox "They match"
Else
  MsgBox "They do not match"
End If

Know though that this is not a foolproof test as TextVal's like B-1_2-2_3
would also show up as matching the Code value B_12-23. Only you know if this
is a "good" enough test as only you know the possible Code and TextVal
values that can be in your data. Other more exacting tests might be
possible, but you would have to tell us about any restraints regarding the
"shape" that the Code and TextVal's could possibly take.
Click to expand...
 
Back
Top