Problem using Match on columns of numbers with leading zeroes formatted 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.
0
u473
3/16/2010 3:11:49 AM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
999 Views

Similar Articles

[PageSpeed] 30

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
0
Dave
3/16/2010 1:07:31 PM
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.
0
u473
3/16/2010 5:52:39 PM
=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
0
Dave
3/16/2010 6:44:38 PM
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.

0
u473
3/17/2010 5:25:24 PM
Reply:

Similar Artilces:

Problem shortcuts in Word
Hello, I work with Word 2008. In the beginning there is no problem, but suddenly i have problem with shortcuts. When i want to do "cmd c" it make an other shortcuts same thing for "cmd s", "cmd v" and surly some other. But when i write there is no problem "c" is "c"... If somebody have an idea please help me. Thank you. Unless you indicate your specific update level of Office as well as OS X & describe what the keystrokes *are* doing contrary to what you expect there isn't much we can offer. -- Regards |:>)...

Avoid plotting refferanced blanks as zeros in scatter plot graphs
I can not figure out how not to plot data that referances blank cells as zero's. I have a worksheet that links to tables with data orginized by size (mm) from big to small. This worksheet contains a scatter plot graph that looks at all sizes in the table. Typically not all sizes have data. The only way I can figure out to remove the zero values from the blank cells is by manualy selecting these cells and clearing the content. I have tried several attempts at differant logic ( if(isnumber(A1),A1,""), and if(isblank(A1),"",A1) ect. and nothing seems to work. ...

How to use CPicturHolder class? #2
Hello, I am fresher for the VC++. I Want to display .jpg, .bmp etc. images in my Dialog box picture controle. I have created one object of of CPictureHolder class as dialog class member. But using that I am not able to show the picture in Picture contrl Plz show simple some code for better understanding...... plz tell me. Klic Klic wrote: > Hello, > > I am fresher for the VC++. I Want to display .jpg, .bmp > etc. images in my Dialog box picture controle. I have created one > object of of CPictureHolder class as dialog class member. > But usin...

encoding problem in Outlook 2007 importing Outlook 2000 personal folders
I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese characters in the subject became unreadable but it's ok to display in message body. In Outlook 2000, there's no problem to display the Chinese characters in the subject. What's wrong with Outlook 2007? What setttings should I change to fix the problem? Thanks. cyl <u8526505@gmail.com> wrote: > I use Outlook 2007 to import the pst file of Outlook 2000. The Chinese > characters in the subject became unreadable but it's ok to display in > message body. In Outlook 2000, there's no problem t...

Outlook 2003 PST problems
I just installed Office 2003 professional on a new computer & am having problems with outlook. I copied my Outlook.pst file into the appropriate directory & although it is recognized the calendar is not working properly. I cannot attach any of the colored labels to my appointments - so everything is pretty much in black & white. I totally uninstalled & reinstalled office... first removing my pst file... but there appears to be an ini file... or something that is retaining some information... does anyone know of a fix for the labels... or how to TOTALLY uninstall office (so I...

EFT remit numbers on an edit list
When printing a check batch that accidentally includes an EFT payment, the user does not have the capability to verify it when printing the edit list as the Remitxxxxx number only appears if you post the batch. The kb article: Payables EFT Option for Printing Remittances Now or Later (851283), says it was entered as a quality report (32944) and development turned it into a suggestion. However, the suggestion does not seem to exist here so I am re-submitting it as this feature has not yet been updated. The suggestion is to automatically assign the REMITXXXXX number to all EFT payments r...

Problem with Authentication
The CRM application was running smooth. But now I can't logging. It ask my credentials and than return a 401.1 error. I had already reinstalled the CRM server but it won't work. I'm accessing it in the same local network. I'm running CRM 3.0 in SBS. Best regards, Erico Hi When you open a Internet explorer and type the name of the CRM server, it prompts for user and password ? Are you logged in with domain credentials ( AD user ) ? If you are, so add url of the crm server in Local intranet. Enable "Automatic logon only in intranet", this is under security,c...

pivot table grand totals not showing for all columns...
i have a pivot table that shows the grand total for some columns, but not for others... there are no differences in the data layout, yet this happens? any clues as to why? and how I can get the grand totals to show for all fields. thanx in the PivotTable floating toolbar, click PviotTable, then Table Options, select 'Grand totals for columns' option. ...

Vista blue screen problem...please help!
I've seen some other threads, here are the dump files: 'RapidShare: 1-CLICK Web hosting - Easy Filehosting' (http://rapidshare.com/files/347103149/Minidump.rar.html) Please, help me out here. -- ceVil It might be better to know at what point the bsod occurs and what the actual err.msg given is "ceVil" <guest@unknown-email.com> wrote in message news:0b9a0c60b90bf6c0be88bda30f2820ab@nntp-gateway.com... > > I've seen some other threads, here are the dump files: > 'RapidShare: 1-CLICK Web hosting - Easy Filehosting' > (...

consective numbering
I use an excel spreadsheet to do monthly invoicing and would like to be able to consectively have a invoice number each time I prepare a new invoice. Is this possible in excel? See this: http://mcgimpsey.com/excel/udfs/sequentialnums.html -- Biff Microsoft Excel MVP "Richard" <Richard@discussions.microsoft.com> wrote in message news:8AE03354-EE7C-4E0C-A86B-0566BB217BA2@microsoft.com... >I use an excel spreadsheet to do monthly invoicing and would like to be >able > to consectively have a invoice number each time I prepare a new invoice. >...

calendar problem
Dear all, I am using both mail and calendar function in outlook 2002. Everything is ok until recently, when I try to make a new appointment or checking any old appointments, an error message "out of memory or resource, try to close some windows..." pop up. The mail function is still ok. Can anyone tell me how can I solve this? I have already installed sp2. The version of office is the professional one with frontpage. Thanks. tp ...

problem #4
Hi, I am facing this new problem... i.e a user has installed Office 2003 on win2k prof platform.. whenever she is working on a outlook.. she gets a Microsoft error message... saying to send the report or don't send the report... Usually I have seen this problem in IE... But for outlook it is new to me... after clicking on send or don't send report.. the outlook closes. And there are no IE open..... and if atall it is open.. the IE does not close... Need your help regards, KAH What is logged to the Event Viewer regarding this? Try one of the following already; http://www.howt...

95 and 97 problem
For some unknown reason there is Excel 95 and 97 on this pc.When i double click on an Excel file, the pc defaults to the 95 program (Which incidently doesn't work properly). So my question is how do i get the pc to default to 97. Cheers Craig Craig, Have a look at Windows Help. From Windows 2000 Help (Index under Programs Associating with File Types) To change which program starts when you open a file In My Computer or Windows Explorer, on the View menu, click Folder Options. Click the File Types tab. In the list of file types, click the one you want to change. Click Edit. In Act...

Date problem
I can't believe there's no post on this, but I can't find it. I've tried the solutions I could find (see below) and still get the error: "The expression is typed incorrectly or it is too complex to be evaluated. For example....Try simplifying...." I have a date field, formatted as Date/Time, General Date, default Value = Now(). I like having the date and the time - in case we need it. I want to run a report on calls taken just for one day. I have CallDate: CallDateAndTime in my query, with "criteria" as Between ([Forms]![frmReports]![txtStartDate]) A...

how to skip a column when entering data?
On one worksheet, for the sake of appearance Column B contains a calculated amount, which is the product of Column A and Column C. Is there a way to skip to column C when entering the data in Column A without doing it manually? Thanks, Godzilla This will give you the idea. right click on sheet tab>view code>insert this>SAVE Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Row > 2 And Target.Column = 1 Then ActiveCell.Offset(, 2).Select End Sub "Godzilla" <Godzilla@monster.org> wrote in message news:MPG.19e3d6a7ae92e03398971a@news....

Counting the number of emails
Hello Is it possible in versions 2000, 2001, 2002, and 2003 of Outlook to count the number of emails received/sent without having to 1, 2, 3, 4, 5...... Many thanks Steve "SteveH" <SteveH@discussions.microsoft.com> wrote in message news:761D1DA9-FA02-49FF-B730-C35D71879512@microsoft.com... > Hello > > Is it possible in versions 2000, 2001, 2002, and 2003 of Outlook to > count > the number of emails received/sent without having to 1, 2, 3, 4, > 5...... > > Many thanks > > Steve What's wrong with looking down at the left end of the sta...

hidden text
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Someone has sent me a message on FirstClass indicating that there is a hidden message embedded within. I couldn't locate it so I pasted the message into Word (thinking that I might be able to locate it this way.) Nope. Can't find how to recover or discover hidden text. No idea -- I'd suggest you check the FirstClass site on how to use the product or get clarification from the sender. I doubt it has anything to do with Mac Office at all. Good Luck |:>) Bob Jones [MVP] Office:Mac On 1/23/10 12:13 PM, i...

how to read XML with entities using C#
Hi there, I have two xml files, one is a master file and the other is just a fragment of xml. Master xml file uses 'DOCTYPE' to define the other file as an entity. Then, the master uses entity references that are supposed to be expanded into real content at parsing time. Examples are provided below. When I open master xml file in InternetExplorer , IE shows correct content. All the entities are transformed into right xml. So far I have been unsuccessfull in getting similar result when I try to do the same programmatically using C#. I was trying to use XmlDocument and XmlVal...

sync problems
I have all of my info on an HP Ipac. I had to reformat the hard drive and I did not make a back-up file. I am trying to move everything back to the computer but it says there is an error with syncing it. How can I move everything from the PDA back to Outlook again? Please help- thanks ...

custom format & saving
Is there a way to save this format type so I can use it in another workbook? The custom format only stays in the workbook it was created in. Also, how do I create a format for something like 12-34-5F or 12-34-56? 00-00-00 works if all digits but won't work in the first example. @@-@@-@@ just repeats everything nor does @-@-@ work. Thanks! Unfortunately, I think the best way for you to do this is to enter it as text. (put a hyphen before your entry) "kdw" wrote: > Is there a way to save this format type so I can use it in another workbook? > The custom format onl...

Office 2007 - HELP
I have used Word for many years and love it - not too thrilled with the changes in Office 2007, but my bigger issue is with Publisher 2007. I have an image that I have used in Word just fine - meaning, the on screen colors are correct and when printed the colors are correct. Someone sent me a Publisher file and asked me to add the image - I did this - now the image is messed up - the colors are correct on screen, but when printed FROM MY computer the image colors are all wrong - but if I transport the file to a different computer it prints fine I have reinstalled Office - no good Any id...

Installation Problem!!
I am having a problem installing MSCRM 1.2. My configuration consists of a Domain Controller running Win 2000 Server SP4 . I am trying to install MSCRM on a member of the domain running Windows 2000 Server SP4. There is a DNS Server on the DC which is integrated with AD, and a SQL Server 2000 running on the Win 2K machine. The install errors out when CRM creates the root business with the message: Setup was unable to install Microsoft CRM Server. Setup was unable to provision your organization. Setup was unable to create the root business. The configuration of ASP.NET seems to be ok, the pr...

Using Checkboxes to add data to a table.
I have been having trouble developing this form for a couple of days. It seems the solution should be simple but so am I. I have an input form with four text boxes. I need to use the same data and create a new line in the table for up to but not always 9 different company positions. I was given a possible way of keeping the info on the form for the next position using the AfterUpdate event and setting the default back to the same box. But in using his suggestion I get #NAME? for the boxes with text when I select the next new record. The boxes with numbers come back the way he said it wo...

Directory Replication Problem #2
Hi, recently my Exchange Server directory database receives changes from other servers but does not send out its own changes. Check from the knowledge base, to correct the USN discrepancy need to use Authrest.exe (need to amke changes for about 100+ users). Does anybody know where to get this file 'Authrest.exe' for exchange server 5.5? Exchange server 5.5 CD only provide this file for ver.4.0. Have anyone use it before? Regards, "Sharon Tan" <sharon_tansk@yahoo.com.sg> wrote: >Hi, recently my Exchange Server directory database >receives changes from ot...

Resource editor problems
Hello all, Under VC++ 7.1... Please consider these two lines: IDC_ARROW_ADD_CANCEL DISCARDABLE "res\\cur00004.cur" IDC_ARROW_ADD_CANCEL CURSOR DISCARDABLE "res\\cur00004.cur" The second is a hand-edited change to the first. Both will compile just fine in the resource compiler. However, after I use a text editor to make this change, I cannot open the .rc file in the resource editor. The resource editor issues the following error open attempting to open the .rc file: error RC2135 : file not found: CURSOR Can anyone shed light on what's going on here? Thanks, Dave ...