Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Text control (VBA/Excel/2000 SP-3)

    I am opening a csv file in Excel and using the data in it to extract data from another Excel file using the Match function. The Match function is entered by VBA code. The cells which are used to interrogate the second file:
    <UL><LI>contain data in the form '000256
    <LI>return an #N/A error[/list] Selecting a cell used to interrogate the second file and setting that cell in the Edit mode then hitting the Enter key, causes the function to return the position of the matching data. I can loop through all of these cells causing each of the Match formulas to return the correct answer, but there are thousands of them and I am looking for an approach that will be faster.
    Any guidance will be greatly appreciated.
    Regards
    Don

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text control (VBA/Excel/2000 SP-3)

    I am not real sure what you are doing. If the value in the cell really does have a single quote in front of the number, then the procedure you describe should do nothing that would cause the Match to work. However, if the procedure you describe is converting a text value into a numeric value (that is what would happen if there was no quote), then there is a quick way to do that to all of the values:

    1- Select an empty cell and enter a 1 into it (that is the number 1).

    2- Select the cell with the one in it and then select Copy from the Edit menu to copy the cell to the clipboard.

    3- Select all of the cells that need to be converted from text to numbers.

    4- Select "Paste Special from the Edit menu.

    5- In the Paste Special dialog box, but a tick mark next to "Multiply" in the operation section.

    6- Click OK. All of the selected values should be converted to numbers.
    Legare Coleman

  4. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text control (VBA/Excel/2000 SP-3)

    Hello Legare
    Thanks for the response. This is a phenomenon that I have seen before but not recently. If memory serves correctly, in my previous encounters it has to do with copying numerical information from a cell that was formatted as Text into a cell with General formatting.
    to give you insight into the current situation, I have attached a sample. In this sample I
    1. <LI>selected cell A5
      <LI>Tapped the F2 key
      <LI>Tapped the Enter key
    This caused cell B5 to change from #N/A to 82
    Any thoughts are most welcome.

    TIA
    Regards
    Don

  5. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Text control (VBA/Excel/2000 SP-3)

    The content of the cells in column A starts with an apostrophe. Excel normally uses this as a marker to specify that the cell is interpreted as text; the apostrophe is displayed in the formula bar but not in the cell itself. In your workbook, the apostrophes are displayed in the cells too, indicating that they are seen as part of the cell value. Edting the cell without changing anything corrects this. You can also select a range with such cells and execute the VBA instruction

    Selection.Value = Selection.Value

    from the Immediate window or in a macro.

  6. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Text control (VBA/Excel/2000 SP-3)

    Thank you Hans
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •