How to launch a procedure on one another sheet?

[frade]

Hello,

I have add one command button with a procedure active on one sheet
called "summary" (please have a look at my code).
The range to consider is not on this sheet but well on another one "TP_REPEAT"

How to change my code?

Public Sub STDEVFUNCTION()
    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
Private Sub CommandButton1_Click()
STDEVFUNCTION
End Sub

Thank you

Regards,

Fran?ois

[Bob Phillips]

Public Sub STDEVFUNCTION()
    With Worksheets("TP_REPEAT")
        iLastRow2 = .Range("A" & Rows.Count).End(xlUp).Row - 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 With
End Sub
Private Sub CommandButton1_Click()
    STDEVFUNCTION
End Sub

[frade]

Thank you!

I think..It doesn't work ..run time erro 1004
I tried also this but without good result

Public Sub STDEVFUNCTION()
iLastRow = Range("A65536").End(xlUp).Row - 1
sResults = Range("B3:B" & iLastRow)
With Worksheets("TP_REPEAT")
iLastRow = .Range("A1").UsedRange.Rows.Count + 1
iLastColumn = .Range("A1").UsedRange.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 With
End Sub

Private Sub CommandButton1_Click()
    STDEVFUNCTION
End Sub

The goal is to calcule the stdev formula for each REF STATUS
In my example (Sheet "TP_REPEAT") in cell B43 =STDEV(B3:B42)
in cell C43=STDEV(C3:C42) and so on..

How to change the code?

FRan?ois

[Insomniac]

Looks like your missing some . (dots) there.

iLastRow = Range("A65536").End(xlUp).Row - 1 
sResults = Range("B3:B" & iLastRow)
'should be
iLastRow = .Range("A65536").End(xlUp).Row - 1 
sResults = .Range("B3:B" & iLastRow)[/vba]
Also you might consider passing the sheet as an argument to the sub so it can be called from any sheet, ie:
[vba]Public Sub STDEVFUNCTION(ByVal Ws As Worksheet)
With Ws
iLastRow2 = .Range("A" & Rows.Count).End(xlUp).Row - 1
'......etc
end with
end sub
 
'called with
Private Sub CommandButton1_Click()
STDEVFUNCTION Me
End Sub

[frade]

Hello Insomniac,


There is a problem with this line ..run time error '13'

sResults = .Range("B3:B" & iLastRow)

What could I do?

Thank you

Fran?ois

[Bob Phillips]

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

[frade]

Ok..thank you for this line

sResults = .Range("B3:B" & iLastRow2).Address

Now, the result appears well for the first column (cell B43 in my example)..but there is
a problem to display the other values (cells C43 and D43 in my example)

Regards

[Bob Phillips]

Private Sub STDEVFUNCTION()
Dim iLastColumn As Long
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim sFormula As String
Dim i As Long
Dim sResults As String
With Worksheets("TP_REPEAT")
iLastRow2 = .Range("A" & Rows.Count).End(xlUp).Row - 1
sResults = .Range("B3:B" & iLastRow2).Address(, False)
iLastRow = .Range("A1").CurrentRegion.Rows.Count + 1
iLastColumn = .Range("A1").CurrentRegion.Columns.Count
.Cells(iLastRow, 2) = "=STDEV(" & sResults & ")"
.Cells(iLastRow, 2).AutoFill .Cells(iLastRow, 2).Resize(, 3)
With .Range("B" & iLastRow)
.Copy .Range("B" & iLastRow).Resize(1, iLastColumn - 1)
.Resize(1, iLastColumn - 1).Value = _
.Resize(1, iLastColumn - 1).Value
End With
End With
End Sub

[frade]

That works!

Thank you

Fran?ois

[frade]

Hello,

I have a last question about the position of the stdev values
on the sheet "TP_Repeat" . For your information, the code is launched
after a click on a button (see sheet "summary"

The values are correct but there are placed on two different rows
In this case, that doesn't work..(in the previous example, it was OK)

What could I change?

Thanks a lot for your advice

Fran?ois