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

[frade]

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]

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]

Hello Norie,

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]

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]

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]

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