Automate the position of a range of formulas?

  • Heres a quick one that must be posible?

    I'm in the process of knocking up a quick reporting system based upon data already in a pivot table. I need to place an aditional column to the right of the Pivot data which will contain formulas to translate the numbers into Rateings, i.e if the pivot total for row 3 is 15 the cell next to it will display "poor" etc.

    My problem lies not in wriring the simple formula's to display the rateing, but in getting to position of these formula's always to be on the right of the pivot, as it will be varing in size dependant upon the amount of data it contains.

    What also may be a problem is that due to the rather draconian formating laws for reports in use at Airbus. I have to copy and paste, as values only, the pivot data into a seperate sheet, so i can apply the correct formatting to the report, all this I have already done via simple macros.

    So really i want the rating thingy on this sheet, in the column to the right of the last column of copied data...................... I hope you can follow this!

    It's probably done with that Offset thing..............

    Any ideas?

  • Assuming that there is only 1 pivot table in the sheet, the code below will display the address of the first cell to the right of the first row of data:

    Hope this helps.



  • Grasshoppa,

    I use this in one of my worksheets:

    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataOnly
        With Selection
            iRow = .Row
            iCols = .Columns.Count
            iCol = .Column + iCols
        End With
        Cells(iRow, iCol).Select

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Many, Many thanks to all!

    This forum should be given the Nobel Prize!
    I will have to re configure my profile to "assumed experience: Well, I can do Charts!".


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!