+ Reply to Thread
Results 1 to 9 of 9

VBA to create specific named ranges and repeating process (Loop?) across columns

  1. #1
    Registered User
    Join Date
    05-23-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    VBA to create specific named ranges and repeating process (Loop?) across columns

    Dear VBA pros,

    I am quite new to VBA but love how it simplifies tasks.

    I have written a basic code (in Module 2) to help create named ranges based on the top row (highlighted cells). It doesnt seem to work and I was wondering if someone could help? I have attached the Excel file.

    For example, the first named range should be "Revenues" and the cells that should be designated are C3 to F6. then I would like to repeat the process to the next set of data, the named range being "Selling Expenses", to cover cells G2 to J6. How can I loop this process till it reaches an empty cell?

    Thanks very much.

    V
    Attached Files Attached Files

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Hi vinothj86
    The reason your code errors is that it's missing a Colon...see .jpg attached.
    semi.jpg
    I know this doesn't answer your broader question (which I'll look at) but thought you might wish to know.

    Are you ALWAYS going to have 4 years and 4 products?
    Last edited by jaslake; 08-24-2012 at 02:54 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Hi vinothj86

    Try this code to name your ranges. Change your Column Heading for Other Expenses to Other_Expenses (no blanks).
    Please Login or Register  to view this content.

  4. #4
    Registered User
    Join Date
    05-23-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Hi jaslake! Thanks so much for your help.

    To answer your first question, the number of products and years will change and that's why I tried to make it dynamic in my basic code. My idea with this is that the no of years and products will influence how far right and down the macro will go before it loops to do the next named range. It will eventually stop when it reaches an empty cell on the far right.

    The other issue is that the headers such as revenues, selling expenses etc will change from time to time but in your code it appears to be defined (within the array function). Is there a way to make this dynamic? Or am I just reading the code wrongly?

    I've just tested your code but it seems to stop after doing "revenues" correctly (i have changed headers with gaps to underscores).

    Thanks again and I look forward to your reply.

    Vinoth

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Hi Vinoth

    I don't understand this as it was tested and ran fine for me on your sample file
    I've just tested your code but it seems to stop after doing "revenues" correctly
    Did you use a different file in your test?

    I'll look at this and get back to you
    the headers such as revenues, selling expenses etc will change from time to time ...Is there a way to make this dynamic?

  6. #6
    Registered User
    Join Date
    05-23-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    I did try it on a different file. Does that make it not work?

    Thanks again jaslake. Much appreciated.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Hi Vinoth

    No that should NOT make it not work but there's something different about the file that IS making it not work.
    Are you able to attach YOUR test file?

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Hi Vinoth

    This code is in the attached and accommodates this
    The other issue is that the headers such as revenues, selling expenses etc will change from time to time but in your code it appears to be defined (within the array function). Is there a way to make this dynamic?
    Please Login or Register  to view this content.
    Let me know of issues.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-23-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: VBA to create specific named ranges and repeating process (Loop?) across columns

    Sorry for the late reply jaslake! Will test it soon and revert. All your help is much appreciated!

+ 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