PDA

View Full Version : Sleeper: How to add a formula at the bottom of a pivot table?



frade
09-13-2005, 04:54 AM
Hello,

I have created a pivot table on the sheet "TP_REPEAT" using VBA (please have a look at my code)

I would like to add automatically (and not manually indeed) a formula 'standard deviation' at the bottom of this table. The position of the row where you can find these formulas change with the number of days (see cell B3 on sheet "Intro"). For instance, in the attached file, It's row 14..also equal to the number of days+4

What could I do?

Thanks a lot by advance

Regards,

Fran?ois

Norie
09-13-2005, 11:49 AM
Francois

Once you have created the pivot table you can find the last row like this.


LastRow= Range("A65536").End(xlUp).Row
You should then be able to use that to put in the STDEV formula.

By the way why are you looping to insert formulas?

Why not just insert the formulas in row 2, then copy that row down the required no of rows?


Cells(2, "G") = "=AVERAGE(IF((" & sDays & "=$B" & i & ")," & sResults & "))"
Range("G2").Copy Range("G2:G" & Lastrow)
Range("G2:G" & Lastrow).Value = Range("G2:G" & Lastrow).Value

frade
09-13-2005, 02:33 PM
Hello Norie, :thumb

Thanks a lot for your help..lastRow function is usefull indeed but I don't understand exactly where I can insert your code. Could you send me the excel file with your modifications?

Regards from Brussels

Fran?ois

frade
09-14-2005, 03:16 AM
Hello Everybody,

In the meantime, I have changed my code..but "#NAME?" appears
in place of the results for this function STDEV..
the main goal is to calculate the STDEV value for each column
(from B to M in my example)

the code on the sheet "TP_REPEAT" is the following one:


Private Sub CommandButton1_Click()
Dim iLastColumn As Long
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim sFormula As String
Dim i As Long
Dim sResults As String
iLastRow2 = iLastRow - 1
sResults = "B3:B" & iLastRow2
iLastRow = Range("A1").CurrentRegion.Rows.Count + 1
iLastColumn = Range("A1").CurrentRegion.Columns.Count
For i = 2 To iLastColumn
sFormula = "STDEV(" & sResults & ")"
Cells(iLastRow, i).Value = Evaluate(sFormula)
Next i
End Sub



What can I do?

Thank you

Fran?ois

Norie
09-14-2005, 03:40 AM
Untested but does this work?


Private Sub CommandButton1_Click()
Dim iLastColumn As Long
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim sFormula As String
Dim i As Long
Dim sResults As String
iLastRow2 = iLastRow - 1
sResults = "B3:B" & iLastRow2
iLastRow = Range("A1").CurrentRegion.Rows.Count + 1
iLastColumn = Range("A1").CurrentRegion.Columns.Count
Cells(iLastRow, 2) = "=STDEV(" & sResults & ")"
Range("B" & iLastRow).Copy Range("B" & iLastRow).Resize(1, iLastColumn-1)
Range("B" & iLastRow).Resize(1, iLastColumn-1).Value = Range("B" & iLastRow).Resize(1, iLastColumn-1).Value
End Sub

frade
09-14-2005, 03:58 AM
Hello Norie,

It's OK now..thanks a lot for your last code..!! I'm not familiar with "Resize" and "copy range". I have only add one line. Otherwise, "#NAME?" appears in place of the good values..


Private Sub CommandButton1_Click()
Dim iLastColumn As Long
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim sFormula As String
Dim i As Long
Dim sResults As String
iLastRow = Range("A65536").End(xlUp).Row
iLastRow2 = iLastRow - 1
sResults = "B3:B" & iLastRow2
iLastRow = Range("A1").CurrentRegion.Rows.Count + 1
iLastColumn = Range("A1").CurrentRegion.Columns.Count
Cells(iLastRow, 2) = "=STDEV(" & sResults & ")"
Range("B" & iLastRow).Copy Range("B" & iLastRow).Resize(1, iLastColumn - 1)
Range("B" & iLastRow).Resize(1, iLastColumn - 1).Value = Range("B" & iLastRow).Resize(1, iLastColumn - 1).Value
End Sub

Have a good day!

Fran?ois