Re: Macro ignoring extracted Values in Cell using GetMatch,Left,Mid or Right

Hi This is David,

This one is weird (at least for me)

Okaii :

1-. In Order to Search and Highlight a small group of Numbers to be inserted In Cell G:2 , I Copy a Macro
(Nicely Excel Guru Mr Jindon did it for me)

Sub Search()
    Dim r As Range, m As Object, myPtn As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D+"
        myPtn = "\b" & .Replace(Range("g2").Value, "|") & "\b"
        .Pattern = myPtn
        For Each r In Range("G6", Range("G" & Rows.Count).End(xlUp))
            r.Font.ColorIndex = xlAutomatic
            For Each m In .Execute(r.Value)
                r.Characters(m.firstindex + 1, m.Length).Font.Color = vbRed
            Next
        Next
    End With
End Sub

2-. In a "Clean" Cell I:5 I Pasted the two following string

Note : Each String have 36 Numbers and i Split it in 18 numbers each part, using Function LEFT, MID and RIGHT

String 1-> 13-03-18-28-30-05-25-11-27-34-04-17-22-35-24-21-26-15-10-01-06-07-12-16-19-32-33-20-29-02-08-31-36-09-14-23
String 2-> 03-04-05-11-13-17-18-21-22-24-25-27-28-30-34-35-01-02-06-07-08-16-19-20-23-26-29-31-33-15-09-12-14-36-10-32

Then i copied String 1 on the following Cells,
a- In Cell C:6 i use the LEFT Function to extract the first 18 numbers

=LEFT(I5,53)

b- In Cell C:7 i use the MID Function to extract the first 18 numbers

=MID(I5,28,53)

c- In Cell C:8 i use the RIGHT Function to extract the first 18 numbers

=MID(I5,28,53)

Then i copied String 2 on the following Cells,
d- In Cell C:9 i use the LEFT Function to extract the first 18 numbers

=LEFT(I5,53)

e- In Cell C:10 i use the MID Function to extract the first 18 numbers

=MID(I5,28,53)

f- In Cell C:11 i use the RIGHT Function to extract the first 18 numbers

=MID(I5,28,53)

At This Point the Cells C6:C8 have 18 of the 36 Uniques Numbers From String #1
and Cells C9:C11 have 18 of the 36 Uniques Numbers From String #2

THEN

3-.I start to Compare Cell C6 against C9 and Using "GetMatch" Function i Extract the Numbers that matched in Cell G:6

=GetMatch(C6,C9)

4-.I start to Compare Cell C6 against C10 and Using "GetMatch" Function i Extract the Numbers that matched in Cell G:7

=GetMatch(C6,C10)

And So On.....


The Problem
=========

5-.When i Insert G:2 a numbers to be search in Column "G" (Place were hold the GETMATCH Function) The Macro
Highlight the whole String

6-.If I modify the Macro In Order to Search In Column "C" (Place that Hold LEFT, MID or RIGHT Fucntion) The Macro
Highlight the whole String as well.

AND

7-If I modify the Macro In Order to Search In Column "I" (The "Clean" Cell that Hold The String ) The Macro Search and Highlight the Numbers
As asked, It Work Great !!


QUESTION : How do I do or what command can i use in order to search in Column "G" and/Or Column "C" ???



Thank you in advance !!


Best Regards

David