+ Reply to Thread
Results 1 to 2 of 2

VBA help for DO LOOP and Offset

  1. #1
    NKeller
    Guest

    VBA help for DO LOOP and Offset

    Hi - I'm trying to create an easy to read table based on data pulled
    from a huge Pivot Table.

    I want the macro to do 2 things that I'm having trouble with...
    1 - repeat the (offset/index/match) function only until it reaches a
    blank cell in the Pivot Table then stop (indicating the end of that
    store's data)

    2 - tell the (offset/index/match) function to increase the offset row
    by 1 each time it runs.

    I realize there's a way to do this with a Do Loop but I haven't been
    able to make the loop stop once it hits a blank cell.

    Any help would be greatly appreciated! I've included my incomplete
    code below. ;-)
    FYI-I'm working in Excel 2000.
    Thanks, in advance, for any help.
    Nicole

    Sub Index_offsetrow()
    '
    ' Index_offsetrow Macro
    ' Macro recorded 7/1/2005 by GECF
    '

    '
    ActiveCell.FormulaR1C1 = _
    "=INDEX('Pivot Table'!R6C1:R28397C2,MATCH(R1C2,'Pivot
    Table'!R6C1:R28397C1,),MATCH(R8C1,'Pivot Table'!R6C1:R6C3,))"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = _
    "=INDEX('Pivot Table'!R6C1:R28397C3,MATCH(R1C2,'Pivot
    Table'!R6C1:R28397C1,),MATCH(R8C2,'Pivot Table'!R5C1:R5C3,))"
    Do
    Range("A10").Select
    ActiveCell.FormulaR1C1 = _
    "=OFFSET(INDEX('Pivot Table'!R6C1:R28297C3,MATCH(R1C2,'Pivot
    Table'!R6C1:R28397C1,),MATCH(R8C1,'Pivot Table'!R6C1:R6C3,)),1,0)"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = _
    "=OFFSET(INDEX('Pivot Table'!R6C1:R28397C3,MATCH(R1C2,'Pivot
    Table'!R6C1:R28397C1,),MATCH(R8C2,'Pivot Table'!R5C1:R5C3,)),1,0)"
    Range("B11").Select
    Loop [Until

    End Sub


  2. #2
    Debra Dalgleish
    Guest

    Re: VBA help for DO LOOP and Offset

    Instead of using a macro, you could use the GETPIVOTDATA function to
    extract data from the pivot table. There are instructions in Excel's
    Help, and some examples here:

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

    NKeller wrote:
    > Hi - I'm trying to create an easy to read table based on data pulled
    > from a huge Pivot Table.
    >
    > I want the macro to do 2 things that I'm having trouble with...
    > 1 - repeat the (offset/index/match) function only until it reaches a
    > blank cell in the Pivot Table then stop (indicating the end of that
    > store's data)
    >
    > 2 - tell the (offset/index/match) function to increase the offset row
    > by 1 each time it runs.
    >
    > I realize there's a way to do this with a Do Loop but I haven't been
    > able to make the loop stop once it hits a blank cell.
    >
    > Any help would be greatly appreciated! I've included my incomplete
    > code below. ;-)
    > FYI-I'm working in Excel 2000.
    > Thanks, in advance, for any help.
    > Nicole
    >
    > Sub Index_offsetrow()
    > '
    > ' Index_offsetrow Macro
    > ' Macro recorded 7/1/2005 by GECF
    > '
    >
    > '
    > ActiveCell.FormulaR1C1 = _
    > "=INDEX('Pivot Table'!R6C1:R28397C2,MATCH(R1C2,'Pivot
    > Table'!R6C1:R28397C1,),MATCH(R8C1,'Pivot Table'!R6C1:R6C3,))"
    > Range("B9").Select
    > ActiveCell.FormulaR1C1 = _
    > "=INDEX('Pivot Table'!R6C1:R28397C3,MATCH(R1C2,'Pivot
    > Table'!R6C1:R28397C1,),MATCH(R8C2,'Pivot Table'!R5C1:R5C3,))"
    > Do
    > Range("A10").Select
    > ActiveCell.FormulaR1C1 = _
    > "=OFFSET(INDEX('Pivot Table'!R6C1:R28297C3,MATCH(R1C2,'Pivot
    > Table'!R6C1:R28397C1,),MATCH(R8C1,'Pivot Table'!R6C1:R6C3,)),1,0)"
    > Range("B10").Select
    > ActiveCell.FormulaR1C1 = _
    > "=OFFSET(INDEX('Pivot Table'!R6C1:R28397C3,MATCH(R1C2,'Pivot
    > Table'!R6C1:R28397C1,),MATCH(R8C2,'Pivot Table'!R5C1:R5C3,)),1,0)"
    > Range("B11").Select
    > Loop [Until
    >
    > End Sub
    >



    --
    Debra Dalgleish
    Excel FAQ, Tips & Book List
    http://www.contextures.com/tiptech.html


+ 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