Automate the position of a range of formulas?

Grasshoppa · Sep 21st 2004

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?
:?

fifijazz · Sep 21st 2004

range("a1").End(xlToRight).Offset(0,1) will get you to the first blank cell in row 1.

**Batman** · Sep 21st 2004

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:

Code

Sub SelectCell()
   
    Dim addr As String, colz As Integer


    ActiveSheet.PivotTables(1).PivotSelect "'1'", xlDataAndLabel
    colz = Selection.Columns.Count
    
    ActiveSheet.PivotTables(1).PivotSelect "'1'", xlLabelOnly
    addr = Selection.Address
    MsgBox Range(addr).Offset(0, colz).Address
    
End Sub

Display More

Hope this helps.

Regards,
Batman.

dangelor · Sep 21st 2004

Grasshoppa,

I use this in one of my worksheets:

Code

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

Grasshoppa · Sep 22nd 2004

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!".

:thanx:

Automate the position of a range of formulas?

Participate now!

Share