+ Reply to Thread
Results 1 to 7 of 7

Create List with VBA

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    England
    MS-Off Ver
    MSO 2016
    Posts
    5

    Create List with VBA

    Hi,

    I would like a VBA code to:
    1) Create a New sheet called "Ranking"
    2) In this new sheet, I want to create a list.
    In A1 I want to have "Ranking", B1 I want to have "Category, C1 I want to have "ID", D1 I want to have "Name"
    From A2 till A1010 a list of numbers: A2 I want to have 1, A3 I want to have 2, A4 I want to have 3, etc.
    From B2 till B38 I want the following list: DOM1, PKG, DOM1, INT, EU, DOM1, PKG, DOM1, INT, EU, DOM1, PKG, DOM1, INT, EU, DOM2, PKG, DOM2, INT, EU, DOM2, PKG, DOM2, INT, EU, DOM2, PKG, DOM2, INT, EU, DOM1, PKG, DOM1, INT, EU

    Any help?

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,130

    Re: Create List with VBA

    Try this.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    01-03-2018
    Location
    England
    MS-Off Ver
    MSO 2016
    Posts
    5

    Re: Create List with VBA

    Thank you! it's working.
    By the way i forgot to mention that I need to loop the following:
    Range("B2:B36").Value = Application.Transpose(Split("DOM1,PKG,DOM1,INT,EU,DOM1,PKG,DOM1,INT,EU,DOM1,PKG,DOM1,INT,EU," & _
    "DOM2,PKG,DOM2,INT,EU,DOM2,PKG,DOM2,INT,EU,DOM2,PKG,DOM2,INT,EU," & _
    "DOM1,PKG,DOM1,INT,EU", ","))

    Basically Range B2:B36 needs to be pasted again and again, immediately below B36 (from B37 till B1010)

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,130

    Re: Create List with VBA

    Kind of thought you might want to do something more with column B.

    Are those definitely the exact values you want repeated down column B?

  5. #5
    Registered User
    Join Date
    01-03-2018
    Location
    England
    MS-Off Ver
    MSO 2016
    Posts
    5

    Re: Create List with VBA

    Value are exactly the same

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    18,130

    Re: Create List with VBA

    That would mean the values in column B would end before/after the values in column A.

  7. #7
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    Excel 2007
    Posts
    4,078

    Re: Create List with VBA

    This will do what you want but as Norie said it would mean column A is 5 numbers short.
    Please Login or Register  to view this content.
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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