Problem using Match on columns of numbers with leading zeroesformatted as text ???

[u473]

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]

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]

I am in total agreement with you that the data has to be the same in
content and format to find a match.
And if not, a clean up is required. I have been through that before.
My first run with text ran fine. My second run with numbers and
leading zeroes, formatted as text, fails.
I am still searching,
What kind of test can I run on each workbook to clue me that the cells
have different content or format ?
Thank you for your advice.
J.P.

 

[Dave Peterson]

=counta(a:a)
will count the number of entries in column A.

=count(a:a)
will count the number of numbers in column A.

I would want these to evaluate to be the same (all entries are numbers) or have
=count() return 0--all entries are text.

For any one particular cell, you can use:
=isnumber(a1)
or even
=count(a1)

 

[u473]

Wooowww !!! You put me on the right track on checking content and
format.
The problem was trailing spaces in one of the two texts to compare.
I had the clue when I started comparing side by side the 2
spreadsheets with the same looking
numbers and obtaining a no match.
I then took a LEN of each cell content and I found that one was 10
characters long vs 8 for the other.
This was a vicious trick because you could not detect the difference
at first glance.
..
Now, how do replace the existing value with a Trim of that value to
remove the trailing spaces ?
Would the following syntax be correct ?
For Each RngCell In X
RngCell.Value = Trim(RngCell(Value)
res = Application.Match(RngCell.Value, IsMatch, 0)
...........
Next RngCell
..
Thank you for your help,
J.P.