+ Reply to Thread
Results 1 to 7 of 7

Loops and relative references?

  1. #1
    Registered User
    Join Date
    11-09-2009
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    52

    Loops and relative references?

    I am looking for some help with loops. This is more for provoking my thought than getting specific about code.

    I have a fully functioning macro that takes texts and sorts it into columns based on set delimeters. It also, for example, clears cells that are not needed and rearranges them based on the order that the next, separate, spreadsheet will use them (it uses formulas that searches for the entry in the first column and takes one cell of data from each row).

    It works great for a single set of data. However, I am running into an issue when I want to paste, say, 10 sets of data, each multiple rows long.

    The code I have used, as an example, is as follows:

    Please Login or Register  to view this content.

    However, this is based on an absolute cell reference. How would I start to go about using a loop with relative references? Example: I would need the above piece of code to run at B2, B6, B10, etc.

    Thanks in advance!
    Last edited by mpkavanagh; 01-13-2011 at 07:28 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,928

    Re: Loops and relative references?

    Please Login or Register  to view this content.


    Note: there is no need to select the cells

    Regards
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-09-2009
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Loops and relative references?

    Is there anyway to set it to continue for all of column A?
    And thanks!

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,928

    Re: Loops and relative references?

    Please Login or Register  to view this content.

    Regards

  5. #5
    Registered User
    Join Date
    11-09-2009
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Loops and relative references?

    Also, is there a way to have several of the statements you posted in a row? For example, I want to manipulate two different cells. As an example, I want to move B4 to C4 then B3 to E3 (and mutiples thereof).

    Please Login or Register  to view this content.
    Thoughts?
    Last edited by mpkavanagh; 01-13-2011 at 06:47 PM.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    31,928

    Re: Loops and relative references?

    Are there going to be many more extensions to the original question?

    Maybe something like (untested):

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    11-09-2009
    Location
    Canada
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Loops and relative references?

    I don't think so. Sorry about extending the original topic.

    To contribute, I found a way to do what I want:

    Please Login or Register  to view this content.
    This will find rows with "SomeValue" in Column A, then move that row's Column B cell to Column E.

    Thanks!
    Last edited by mpkavanagh; 01-13-2011 at 08:07 PM.

+ 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