+ Reply to Thread
Results 1 to 15 of 15

How to use VBA to assign a reference number for each item?

  1. #1
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    How to use VBA to assign a reference number for each item?

    How to assign a reference number for each item?
    For examples,
    today is 10/7/2013, I want to assign a reference number 20130710001 for the first item,
    10072013 mean 10/7/2013, 001 mean the first item.
    Thus, 20130711001 means the second item of 11/7/2013.

    How can I use VBA to assign a reference number for each item?

    Thanks, jason.

    Here is my code, I have set iRow before. But I cant use this code to do the following requirement
    17/7/2013, I enter one data --> it output 20130717001
    18/7/2013, I enter one data --> it output 20130718002 , how can I change it to 20130718001??

    Please Login or Register  to view this content.
    Last edited by jasonlewis; 07-25-2013 at 02:43 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to use VBA to assign a reference number for each item?

    For cel A1 and drag down.

    dutch =TEKST(VANDAAG();"dd-mm-jjjj")&TEKST(RIJ();"00")

    english =TEXT(today(),"dd-mm-yyyy")&TEXT(ROW(),"00")
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Thanks, but how can I use VBA to write the same code?

  4. #4
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Did someone know how to solve it?

  5. #5
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Did someone know how to solve it?

  6. #6
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Did someone know how to solve it?

  7. #7
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Did someone know how to use VBA to assign a reference number for each item?

  8. #8
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: How to use VBA to assign a reference number for each item?

    Is this any use?
    Attached Files Attached Files
    *******************************************************

    HELP WANTED! (Links to Forum threads)
    Trying to create reusable code for Custom Events at Workbook (not Application) level

    *******************************************************

  9. #9
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Re mc84excel,
    wow, so many code, are they all necessary?
    or could you make it simple, as it is hard to understand them
    Thanks!

  10. #10
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Did someone know how to use VBA to assign a reference number for each item?

  11. #11
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: How to use VBA to assign a reference number for each item?

    Hi Jason,
    could you show an example of a file with your original data and the desired result

  12. #12
    Valued Forum Contributor
    Join Date
    08-29-2012
    Location
    In lockdown
    MS-Off Ver
    Excel 2010 (2003 to 2016 but 2010 for choice)
    Posts
    1,766

    Re: How to use VBA to assign a reference number for each item?

    Quote Originally Posted by jasonlewis View Post
    Re mc84excel,
    wow, so many code, are they all necessary?
    or could you make it simple, as it is hard to understand them
    Thanks!
    No they are not all necessary. You sounded desperate for a solution to this thread so I took a few minutes to throw something together. I just copied a few of my templates that I knew contained the functions I would need. I didn't have the time when creating to remove all the template functions that weren't required.

    I guess the first question is - does the macro I created produce the outcome you require? (Use the macro list on the Developer tab - it's the only macro on the uploaded WB)

    In any case, it looks like nilem is stepping in to help you out. (Thanks nilem)

  13. #13
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Thx everyone.
    Re mc84excel, I will take a look on your excel file l8r
    Re nilem, here is my sample excel form, I want to make the reference no. look likes wht I have uploaded.
    Attached Files Attached Files

  14. #14
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: How to use VBA to assign a reference number for each item?

    Hi Jason,
    try to enter the date in column B, a reference number will appear automatically
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    06-29-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2007
    Posts
    50

    Re: How to use VBA to assign a reference number for each item?

    Re nilem,

    thx a lot!! and it works.

+ 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. [SOLVED] How to assign a macro to autorun when a particular list item is chosen?
    By RatHat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2013, 04:08 PM
  2. Assign Reference Number to Blank Cells
    By BSOD14 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2013, 10:48 AM
  3. Excel 2008 : Assign Vendor to Item Number
    By DADADO in forum Excel General
    Replies: 2
    Last Post: 07-21-2012, 03:04 AM
  4. Replies: 7
    Last Post: 02-10-2012, 01:40 PM
  5. Can I assign a macro to each item in a drop-down list?
    By jbp20717 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2005, 09:05 AM

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