+ Reply to Thread
Results 1 to 4 of 4

Reformatting - insert formula with offset, then offset to next cell

  1. #1
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Reformatting - insert formula with offset, then offset to next cell

    I have a little complicated formatting issue which I need to address via VBA in order to get references and values aligned in one row, so that I can later on look them up via a VLOOKUP formula.

    right now, I would a need a macro which helps me to perform the following steps within column A, ranging from A2 to the last cell with content in column B (offset -1 to clolumn A)

    1. cell A2 should display the value of B3 ( means write formula =b3 into cell A2)
    2. offset by 4 rows from A2 to A6
    3. cell A6 should display the value of B7
    4. offset by 4 rows from A6 to A10
    repeat....

    repeat this until the last cell of the range is reached, then copy column A and paste the values to get rid of the formulas.

    I have no clue how to go about macro. Would much appreciate if someone could provide me some code to get there.
    Thank you very much!

    A2k

  2. #2
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Reformatting - insert formula with offset, then offset to next cell

    This code may be an idea:
    Please Login or Register  to view this content.
    Regards,
    Antonio

  3. #3
    Forum Contributor
    Join Date
    03-24-2009
    Location
    Vietnam
    MS-Off Ver
    Excel 2010
    Posts
    382

    Re: Reformatting - insert formula with offset, then offset to next cell

    that helps a lot, thanks!

    but if i want to display the specific cell b&r+1, but some others in the same row as well, how do i have to write the formula? I tried to insert two more cells but dont get very far...

    Please Login or Register  to view this content.
    the outcome should be something like "=B18&" - "&D17&" - "&G17" in cell A17.

    thank you!

  4. #4
    Forum Expert
    Join Date
    11-23-2005
    Location
    Rome
    MS-Off Ver
    Ms Office 2016
    Posts
    1,628

    Re: Reformatting - insert formula with offset, then offset to next cell

    You could use this code:
    Please Login or Register  to view this content.
    Regards,
    Antonio

+ 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