+ Reply to Thread
Results 1 to 12 of 12

ing header names as array in order to be used later

  1. #1
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    ing header names as array in order to be used later

    Please Login or Register  to view this content.
    I basically want to loop through the header of a current region, save the header names into an array. Later I will use these values to assign names to the columns. THis code is supposed to store all of the names in the headerrange in an array of headname. To set the size of the array, I've used the number of columns. My first error is for the Set headercols = headers.columns.Count. it says an object is required. I'm not certain what to do right now. I'm learning on the fly a bit so. The rang for the header works, although I know its not a very elegant solution. My questions is how to get this loop going so I can store the values in the array. help would be much appreciated.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ing header names as array in order to be used later

    no need to loop
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: ing header names as array in order to be used later

    JosephP,

    That was amazing! Thank you. Your solution makes me feel a bit silly, but struggling is part of learning I guess.

    Can I simply use the values as an array to name Ranges now? Specifically, I need to assign these header to the names of the data range that are immediately below. I have some idea of how to do this but it is very convoluted.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ing header names as array in order to be used later

    yes-headname is a two dimensional array with one row and multiple columns so you can loop through it to name your columns. are you creating named ranges? if so perhaps using a table would make more sense

  5. #5
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: ing header names as array in order to be used later

    I am using name ranges. I want the ranges of certain data columns and data row to be named automatically from data dumps, and for the user to easily name them. I'm not certain how a table would help with this. Do you have link on tables that automatically provide a named range? apologies if this is basic excel knowledge.

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ing header names as array in order to be used later

    tables automatically give you dynamic named ranges which you can refer to in formulas or use as the source for a pivot table: http://office.microsoft.com/en-us/ex...010155686.aspx

  7. #7
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: ing header names as array in order to be used later

    Thanks again JosephP. So the issue is that I need to use these named ranges with the Indirect function later. that is the whole purpose of this project. I'm trying to have an assortment of way to name ranges horizontally and vertically so that they can be referred to by someone else via INDIRECT, MATCH, etc. I'm concerned that tables, with the additional naming convention would complicate this. I would gladly put it in a table, but I don't think that's a viable option. All of the formulas are set out to use name ranges based on column headers.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ing header names as array in order to be used later

    if all you need to do is name all the columns of the current region using the first row it's actually real easy
    Please Login or Register  to view this content.
    :-)
    Last edited by JosephP; 09-11-2013 at 09:32 AM.

  9. #9
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: ing header names as array in order to be used later

    Simple solutions keep popping up. I've looked at the CreateNames documentation but I don't know exactly how to make the name local. before I was going to piecemeal by something like:

    Please Login or Register  to view this content.
    this is obviously far from the simpler options you presented. is there any way to have similar solution but using the CreateNames, rather than the Names.Add?

    Sorry if this is a bit off topic, I should have mentioned how the names HAVE to be local.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ing header names as array in order to be used later

    createnames can't do local names unfortunately so you would need a loop
    Please Login or Register  to view this content.
    oughta do it

  11. #11
    Registered User
    Join Date
    07-19-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2013
    Posts
    36

    Re: ing header names as array in order to be used later

    Hey JosephP,

    That is exactly it! Also, I don't know why I wasn't using 'with' before. I think I fully understand why that works. You are an amazing help!

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: ing header names as array in order to be used later

    you're welcome :-)

    please don't forget to mark the thread solved (click the 'thread tools' link at the top, then 'mark solved')

+ 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. Replies: 0
    Last Post: 08-29-2013, 10:07 PM
  2. [SOLVED] Copy columns by header value and paste them in a specific order into another workbook
    By KeriM in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2012, 11:13 AM
  3. Sorting Data into Time order using a String header
    By beat in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-27-2012, 06:06 AM
  4. Create array of file names/sheet names
    By BVHis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-06-2008, 11:30 AM
  5. [SOLVED] Header names
    By Mo in forum Excel General
    Replies: 2
    Last Post: 01-12-2005, 12:06 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