+ Reply to Thread
Results 1 to 4 of 4

VBA Script for formatting numbers

  1. #1
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    VBA Script for formatting numbers

    Hi,

    I'm creating a template for a reconcilliation.
    The following problem occurs:

    Sheet 1 has a VLOOKUP which searches numbers in the range 100000-999999 in sheet 2.
    Sheet 2 has a range (A:A) with numbers 1-999999.

    Number 1 in sheet 2 should be 100001, number 2 should be 100002, and so on.
    So at the end of sheet 2 i added the formula =text(A2,100000) so the format will be 1XXXXXX.

    The problem is now that the VLOOKUP in sheet 1 does not work and will result in #N/A and that is offcourse because the formula i used in sheet 2 does not make a value.

    Now without copy & Pasting this as values, is there a script i can use that automatically transfers the numbers to this format?

    The collumn where i put the formula now is V.

    Thanks for your help and advice.

    Regards,
    Crispy85

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Script for formatting numbers

    Do you have iferror included with your formula? The N/A might resulted from no match.

  3. #3
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: VBA Script for formatting numbers

    Hi AB33,

    No i did not use iferror or isna.
    But instead of using the formula in sheet 2 ( text(a2,"100000") i just manually entered 100024 for example, and then the vlookup in sheet 1 resulted in 100024.

    Regards,
    Crispy

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: VBA Script for formatting numbers

    If the Vlookup formula is looking for exact match and does not find one, it will result in N/A, but if you include iferror, you can use zero or blank to replace N/A. I suspect the formula is probably looking for next match, not EXACT match.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Is there any script that I generate 6 numbers of these numbers from the list?
    By dragon66 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2014, 01:22 PM
  2. [SOLVED] VBA script to sort for non duplicate numbers
    By madhatter40 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 09:40 PM
  3. [SOLVED] Conditional formatting - formatting the lowest 2 numbers in a list of 12 numbers
    By ktbb0312 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-09-2013, 09:59 AM
  4. SCRIPT: custom formatting
    By nastech in forum Excel General
    Replies: 0
    Last Post: 04-27-2006, 12:00 PM
  5. Formatting Excel within ActiveX script
    By ninel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-05-2006, 09:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1