+ Reply to Thread
Results 1 to 15 of 15

Marking columns/rows

  1. #1
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171

    Marking columns/rows

    Usually when I try to mark a row/column for future reference I code the following:


    Please Login or Register  to view this content.
    So in the code, strow and stcol are row and col markers respectively.
    However, I now have to do this for 45 columns, therefore my way is far too intensive, is there an easier way to do this for a large no of cols. Thanks in advance.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Same word or different words?

    Have you consider using Name ranges and using that in the code

    E.g Name Range called FirstCol

    Please Login or Register  to view this content.
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    so if I have one cell in a sheet called 'TBASIC Amount'
    and I need to mark the column, can I use

    Please Login or Register  to view this content.
    This doesnt seem to work

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    You use a name range as per attached?

    http://www.contextures.com/xlNames01.html

    VBA Noob

  5. #5
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Thanks,

    I'll elaborate on my scenario, say I have raw data of 45 cols, with col names:

    GBTAX
    GBERP
    GBPPP

    etc.etc.

    and I need to name these columns via a macro which I run, how do I code?

  6. #6
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    with col names
    You mean headings??

    You can use the Name range method if you name the first cell in the col or do a loop with the find command

    VBA Noob

  7. #7
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    So inthe code below Im passing an array through a FIND statement and for each variable Im trying to create diff row & col names, not quite working, thank you in advance for any suggestions

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Maybe

    Please Login or Register  to view this content.
    VBA Noob

  9. #9
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Many thanks, this is close to what I wanted, to elaborate (dont think I was clear), in the last section of code a range of values are passed through variables irow & icol.
    What I wanted are the creation of variables for each column:

    e.g.
    GBTAXrow
    GBTAXcol
    GBERProw
    GBERPcol
    GBPPProw
    GBPPPcol

    etc.etc.

    I have tried:

    Please Login or Register  to view this content.
    This doesnt quite work

    Many Thanks
    Last edited by T De Villiers; 11-19-2008 at 06:47 PM.

  10. #10
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171

    Array and Fins

    Hi,

    Im trying to create a number of variables:
    GBTAXRow, GBTAXcol, GBERPRow, GBERPcol, GBPPPRow, GBPPPCol

    The asterixed lines below are not working, thank you in advance,


    Please Login or Register  to view this content.

  11. #11
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Thanks for this Bob,

    The attached should make things clear.

    I want the code to return the variables (relating to the row and col numbers, see attached)

    GBTAXrow = 4
    GBTAXcol = 3
    GBERProw = 4
    GBERPcol = 4
    GBPPProw = 4
    GBPPPcol = 5

    I can then refer to these variables later.
    There will actually be 45 values within the array and could be positioned differently which is why I require this code.

    Thank you in advance.
    Attached Files Attached Files

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You could do it like this:
    Please Login or Register  to view this content.
    ... but that code is a little delicate. How about this:
    Please Login or Register  to view this content.
    Last edited by shg; 11-20-2008 at 12:41 AM.
    Entia non sunt multiplicanda sine necessitate

  14. #14
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    T De Villiers,

    Please read forum rules below. Duplicate posts merged.

    VBA Noob

  15. #15
    Forum Contributor
    Join Date
    08-20-2005
    Posts
    171
    Okay, acknowledged, and many thanks for your help on this

+ 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