Apply an excel function to a column in a matrix

[camqui]

Hi everybody

I'm looking for some help applying the standard deviation to one column in a matrix, I don't know the right way to define the range in this formula, so I will put an example for a better explanation of what I intend to do:

Option Base 1

Sub Producto()


ReDim c(6, 2) As Variant


c(1, 1) = 10
c(1, 2) = 15
c(2, 1) = 17
c(2, 2) = 74
c(3, 1) = 16
c(3, 2) = 83
c(4, 1) = 75
c(4, 2) = 15
c(5, 1) = 78
c(5, 2) = 54
c(6, 1) = 26
c(6, 2) = 56


Dim e As Double


e = application.WorksheetFunction.StDev_P(c(1,1)6,1))




End Sub


​

When I hit enter after typing <e = application.WorksheetFunction.StDev_P(c(1,1)6,1))> There's a message displying a compilation error: "Expected: list separator or )" and the code highlight the ":"

This is an example so I can't define item by item to apply this function. I hope you've understood me and I will thank a lot anyone who help me[h=3][/h]

 

[camqui]

thanks emoticons, what I wanted to say is:

e = application.WorksheetFunction.StDev_P(c(1,1)6,1))

 

[Norie]

All you need to do is pass the matrix/array to the function.

e = Application.WorksheetFunction.StDev_P(c)

 

[camqui]

Hi, thanks for your help. The number of columns in the matrix is variable since it will be a selection, so: How can I split a matrix of n columns to n arrays? where n is variable?

 

[Norie]

Are the columns to be passed to the function contiguous?

 

[camqui]

They're contiguous

 

[Norie]

It should be straightforward then, to get the appropriate range we can use offset and resize.

 

[camqui]

Hi Norie, could you post some code as an example, I couldn't find info abot resize, thanks!

 

[mikerickson]

If you have an array, myArray, this will return the n'th column of myArray

msgBox  Worksheetfunction.StDevA(WorksheetFunction.Index(myArray, 0, n))