+ Reply to Thread
Results 1 to 10 of 10

Using offset to loop?

  1. #1
    Registered User
    Join Date
    06-19-2015
    Location
    Michigan
    MS-Off Ver
    2010
    Posts
    30

    Using offset to loop?

    Hello everyone,

    I am trying to rearrange some data I have in a spreadsheet that I pulled from a report.

    Child Name Grade Parent 1 Phone 1 Parent 2 Phone 2
    Child Name 2 Grade Parent 1 Phone1 Parent 2 Phone 2

    This is how it looks, but I want to be able to copy Parent 2 and Phone 2 and paste it directly underneath Parent 1 and Phone 1 so it looks like this:

    Child Name Grade Parent 1 Phone 1
    Parent 2 Phone 2
    Child Name 2 Grade Parent 1 Phone 1
    Parent 2 Phone 2

    I am hoping to do this with a loop so that it will check if a cell in A is empty, and if so to copy the information from the 5th and 6th cells in the row above and paste them in the 3rd and 4th below. When it's done, I would like the loop to end if there are 3 consecutive empty cells in a row in column A.

    I have absolutely no idea where to start, I believe this would be done using offsets, but I don't know how to use offsets in a loop. Anybody have any ideas?

    You all rock!

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Using offset to loop?

    "to end if there are 3 consecutive empty cells " why, is there other data after the3 empty cells?
    Just to stop at the last cell in column "A" is not good ?
    - Battle without fear gives no glory - Just try

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Using offset to loop?

    Maybe:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    4,492

    Re: Using offset to loop?

    Try
    Please Login or Register  to view this content.
    Last edited by PCI; 09-22-2015 at 02:48 PM.

  5. #5
    Registered User
    Join Date
    06-19-2015
    Location
    Michigan
    MS-Off Ver
    2010
    Posts
    30

    Re: Using offset to loop?

    Quote Originally Posted by JOHN H. DAVIS View Post
    Maybe:

    Please Login or Register  to view this content.
    That works great! Thank you so much!

    Is there any way this can work with cutting more than 2 cells? I know I didn't specify that in my original example, my apologies.
    I tried this:
    Please Login or Register  to view this content.
    and it returned an error.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Using offset to loop?

    You're welcome. Glad to help out and thanks for the feedback. Try:

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-19-2015
    Location
    Michigan
    MS-Off Ver
    2010
    Posts
    30

    Re: Using offset to loop?

    Quote Originally Posted by JOHN H. DAVIS View Post
    You're welcome. Glad to help out and thanks for the feedback. Try:

    Please Login or Register  to view this content.
    Awesome! I really appreciate your help.
    I keep mixing things up in my spreadsheet, but this should be the last little tweak I ask you for.
    Instead of pasting the information in the third column, how would I be able to paste it in the 5th column?

    Thank you so so much!

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Using offset to loop?

    Maybe:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-19-2015
    Location
    Michigan
    MS-Off Ver
    2010
    Posts
    30

    Re: Using offset to loop?

    This is perfect! Thank you!!!

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Using offset to loop?

    Again, Welcome. Glad it works for you. Please comply with Forum Rules and mark this thread as SOLVED. See link for guidance.

    http://www.excelforum.com/forum-rule...rum-rules.html

+ 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. VBA - Offset to the next line and loop
    By the3rdman77 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-15-2013, 08:42 PM
  2. [SOLVED] For..Next, offset, nested loop
    By Gingin8639 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-05-2012, 10:23 AM
  3. VBA Loop & Offset Syntax
    By bgintheusa in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-23-2010, 09:06 PM
  4. Using offset with in a loop
    By hoffey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-30-2009, 05:49 PM
  5. Loop & Offset assistance required
    By Digory in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-20-2007, 04:30 PM
  6. Loop with activecell.offset
    By mthomas in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2005, 05:29 PM
  7. [SOLVED] Need offset range for loop
    By Joe Fish in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-16-2005, 09:05 PM
  8. VBA help for DO LOOP and Offset
    By NKeller in forum Excel General
    Replies: 1
    Last Post: 07-04-2005, 08:05 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