Problem using Match on columns of numbers with leading zeroesformatted as text ???
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. |
Problem using Match on columns of numbers with leading zeroesformatted as text ???
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 |
Problem using Match on columns of numbers with leading zeroesformatted as text ???
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. |
Problem using Match on columns of numbers with leading zeroesformatted as text ???
=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 wrote: 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 |
Problem using Match on columns of numbers with leading zeroesformatted as text ???
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. |
All times are GMT +1. The time now is 01:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com