looking for macro code to create dynamic range
looking for macro code to create dynamic range
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: looking for macro code to create dynamic range

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I just learned I can not use OFFSET in VBA, I am therefore looking for macro code that will create a dynamic range for running a pivot table. thanks.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can set up a dynamic range and assign it to a range name then used that range name in your macro.

    See the following regarding setting up a dynamic range.

    http://www.ozgrid.com/Excel/DynamicRanges.htm
    It's never too late to learn something new.

    Ricky

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-18 12:00, deuxo wrote:
    I just learned I can not use OFFSET in VBA, I am therefore looking for macro code that will create a dynamic range for running a pivot table. thanks.
    Yes you can use OFFSET in VBA.
    If you explain further what you are trying
    to do then perhaps someone can help you.

    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i think poster wants to say offset function avialable in worksheet...to be used in vba code.

    Yes you can put formula with offset function by vba code..but i think poster does not want that.

    i think that feature is not avialable in vba code.. ofcourse you can use standard offset function available to move around the cell.

    well for your dynamic range if you want to use in vba code may you can get some idea.. from my file nos. 16 'populating combobox'

    http://www.pexcel.com/download.htm

    here i have used dynamic range to populate all the combobox.

    if you want to use in formula you can ofcourse use offset function..

    =SUM(OFFSET(G3,0,0,COUNTA(G:G)))

    you modify above formula..to your need.

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    I don;t know if this is the sort of thing you want to be doing, but, I am in the middle of developing pivot tables based on dynamic ranges too, take a look at this. Here I have actually made the dynamic range into an array and looped to create the table, take a look


    Set rngcolhead = Range(Cells(1, p), [IV1].End(xlToLeft))
    z = rngcolhead.Cells.count
    arrcolhead = rngcolhead

    For k = 1 To z Step 1
    Debug.Print arrcolhead(1, k)


    Application.StatusBar = "Calculating pivot field " & k & " of " & z

    Application.Calculation = xlManual
    Sheets("PivotSheet").Select

    With ActiveSheet.PivotTables("PivotTable1").PivotFields(arrcolhead(1, k))
    .Orientation = xlDataField
    .Function = xlSum
    .Caption = "Sum of " & arrcolhead(1, k)
    .Position = k

    End With

    Next k


    Hope this helps,

    RET79

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com