I want to calculate the stdev of (colA - colB) in VBA code without
creating a new column. My attempted code below gives me a type
mismatch. Does anyone know how I would do it?

Public Sub calcStdev()

Dim myRangeAct As Range
Dim myRangeMod As Range

Set myRangeAct = Range(Cells(1, 1), Cells(100, 1))
Set myRangeMod = Range(Cells(1, 2), Cells(100, 2))

Debug.Print Application.WorksheetFunction.StDevP(myRangeAct)
Debug.Print Application.WorksheetFunction.StDevP(myRangeMod)

'GET TYPE MISMATCH
Debug.Print Application.WorksheetFunction.StDevP(myRangeAct -
myRangeMod)

End Sub

Thanks

Try...

msgbox [stdevp(MRng)]

Where MRng is a named range.
Add more references if needed.


Regards
Robert McCurdy
"pb" wrote in message oups.com...
I want to calculate the stdev of (colA - colB) in VBA code without
creating a new column. My attempted code below gives me a type
mismatch. Does anyone know how I would do it?

Public Sub calcStdev()

Dim myRangeAct As Range
Dim myRangeMod As Range

Set myRangeAct = Range(Cells(1, 1), Cells(100, 1))
Set myRangeMod = Range(Cells(1, 2), Cells(100, 2))

Debug.Print Application.WorksheetFunction.StDevP(myRangeAct)
Debug.Print Application.WorksheetFunction.StDevP(myRangeMod)

'GET TYPE MISMATCH
Debug.Print Application.WorksheetFunction.StDevP(myRangeAct -
myRangeMod)

End Sub

Thanks

pb -

A non-VBA solution is

=STDEVP(A1:A100-B1:B100)

array-entered (Control+Shift+Enter instead of Enter).

- Mike
http://www.mikemiddleton.com


"pb" wrote in message
oups.com...
I want to calculate the stdev of (colA - colB) in VBA code without
creating a new column. My attempted code below gives me a type
mismatch. Does anyone know how I would do it?

Public Sub calcStdev()

Dim myRangeAct As Range
Dim myRangeMod As Range

Set myRangeAct = Range(Cells(1, 1), Cells(100, 1))
Set myRangeMod = Range(Cells(1, 2), Cells(100, 2))

Debug.Print Application.WorksheetFunction.StDevP(myRangeAct)
Debug.Print Application.WorksheetFunction.StDevP(myRangeMod)

'GET TYPE MISMATCH
Debug.Print Application.WorksheetFunction.StDevP(myRangeAct -
myRangeMod)

End Sub

Thanks

Hello,

In VBA the operator "-" is not defined on ranges.

A workaround:
Sub calcStdev()

Dim myRangeAct As Range
Dim myRangeMod As Range
Dim a(1 To 100) As Double
Dim i As Long

Set myRangeAct = Range(Cells(1, 1), Cells(100, 1))
Set myRangeMod = Range(Cells(1, 2), Cells(100, 2))

Debug.Print Application.WorksheetFunction.StDevP(myRangeAct)
Debug.Print Application.WorksheetFunction.StDevP(myRangeMod)

For i = 1 To 100
a(i) = myRangeAct(i) - myRangeMod(i)
Next i

Debug.Print Application.WorksheetFunction.StDevP(a)

End Sub

Regards,
Bernd