PDA

View Full Version : Array raising each element to a power - please help!



ana
09-22-2012, 08:18 PM
Hi, I have written code which creates an array of 100 elements and then each element is an input matrix (Min) which the user enters into the worksheet raised to a power. I think the logic to the code is right as it keeps on calling the MMult function however I get the error 'Run-time error 1004' Unable to get the MMult property from the Worksheet Function.

Can anyone please let me know what I am doing wrong?




Dim oS(99) As Variant
Dim x As Variant
Dim power As Integer
Dim Min As Variant

power = 5 'This is the power to which I'd like the elements of the array raised to

Min = Range(Worksheets("1").Cells(3, 3), Worksheets("1").Cells(8, 8)) 'This is the matrix that the user enters into the specified worksheet range

For x = 1 To 99
oS(x) = WorksheetFunction.MMult(oS(x), Min)
Next



Thanks,
Ana x

snb
09-23-2012, 05:39 AM
Sub snb()
Range("C3:H8").Offset(10) = [index(C3:H8^5,)]
End Sub

patel
09-23-2012, 06:25 AM
@ana
The number of columns in array1 must be the same as the number of rows in array2, and both arrays must contain only numbers.
Array1 and array2 can be given as cell ranges, array constants, or references.
MMult returns the #VALUE! error when:
Any cells are empty or contain text.
The number of columns in array1 is different from the number of rows in array2.
The size of the resulting array is equal to or greater than a total of 5,461 cells.

@snb
can you explain me your very good code ?

ana
09-23-2012, 04:56 PM
@ patel

Thank you very much for your help! I figured out my problem it was because the array oS(x) was empty so you were right. I just entered in




oS(x) = Min



In the code and it filled array with the input matrix and was then able to raise it to a power.

@ snb I didn't quite understand what your code meant but thank you for the help anyway

Ana x