Just like when you use the formula in excel, =match() expects an exact, er,
match -- well, with that 3rd argument 0 or false.

I'd fix the data to be consistent.

I'd either change all the values in the table to be real numbers or all text.
And make sure that the value to match on was the same -- either text or numbers.

You could adjust your code to look for either/both, but that won't help in any
formula you use later--or any code that doesn't include this kind of
work-around.

And remember, just changing the format of the cell won't change the value of the
cell. More work needs to be done.

I'd do my best not to use this--I think it's a mistake to not clean the data,
but if you wanted:

For Each RngCell In X
MsgBox RngCell.Value

'look for a match with text
res = Application.Match(RngCell.Value & "", IsMatch, 0)
If IsError(res) Then
'look for a match for a number
res = application.match(clng(rngcell.value), ismatch, 0)
end if

'check the results from either
if iserror(res) then
'no match
Else
'Match found
RngCell.Interior.Color = vbYellow
End If
Next RngCell

This won't find a match between:

'00003
and
'0003

That one of the reasons to clean the data first.

u473 wrote:

In my test run, I was using a column of first names in workbook A to
check and flag their
presence in workbook B, The code given to me by Marcus here worked
fine. No problem at all.
.
When I switched to the real problem by replacing my columns of data
with numbers
with leading zeroes, the columns being already formatted as text, the
program did not find Matches.
.
In both workbooks columns of data, cells have an automatic Excel
comment that says :
"The number in this cell is formatted as text or preceded by an
apostrophe".
.
What am I missing here ?
Is there a special care to be taken when using numbers with leading
zeroes in a cell formatted as text ?
Here is the code :

Sub FlagMatchingRecords()
Dim RngCell As Range
Dim IsMatch() As Variant
Dim res As Variant
Dim lw As Long
Dim lr As Long
Dim X As Range
Dim wb As Workbook
Dim ws As Worksheet
lr = Range("B" & Rows.Count).End(xlUp).Row
Set wb = Workbooks("B.xls")
Set ws = wb.Sheets("Sheet1")
IsMatch() = Range("B1:B" & lr).Value
lw = ws.Range("A" & Rows.Count).End(xlUp).Row

Set X = ws.Range("A1:A" & lw)
For Each RngCell In X
MsgBox RngCell.Value
res = Application.Match(RngCell.Value, IsMatch, 0)
If IsError(res) Then
'No Match
Else ' Match
RngCell.Interior.Color = vbYellow
End If
Next RngCell
End Sub

Help appreciated,

J.P.

--

Dave Peterson