Cook09

asked on

# Multiplying an Array by another Array

I have a Range

rngRow.Value2.Value2(1,1) = 10

.Value2.Value2(1,2) = 30

.

.

.Value2.Value2(1,12) = 300

The second array is a series of 12 constants with a Defined Name

I would like to either multiply or divide the

or I guess another option or way of explaining would be:

which is ultimately what I would like to have the flexibility to perform.

**rngRow("H14:N14")**that is populated with values. Depending on the values, the Locals Window shows that:rngRow.Value2.Value2(1,1) = 10

.Value2.Value2(1,2) = 30

.

.

.Value2.Value2(1,12) = 300

The second array is a series of 12 constants with a Defined Name

**CatCost**or {1,2,3,4...12}I would like to either multiply or divide the

**rngRow.Value2**Array by the**CatCost**array and those values become a third array.**varResult = rngRow.Value2 * vCost**'It would become an array with 12 calculated values*Is there a method for performing multiplication or division on two arrays of equal numbers?*or I guess another option or way of explaining would be:

**varResult = Range1("A1:A12") * Range2("C1:C12")**which is ultimately what I would like to have the flexibility to perform.

It's assigned to cells like this:

Worksheets("Sheet1").Range("E1:E3").FormulaArray = "=Sum(R1C1:R3C3)"

Worksheets("Sheet1").Range

By using FormulaArray it converts an ordinary formula into an array formula and in so doing adds the curly brackets.

Patrick

Patrick

But then no doubt someone else will correct me...

Or perhaps:

Worksheets("Sheet1").cells(1,"A").FormulaArray = "=SUM(A1:A12")*C1:C12)"

See example in attached file. It's in the macro called 'fred'!

Patrick

array-formula-01.xls

Worksheets("Sheet1").cells

See example in attached file. It's in the macro called 'fred'!

Patrick

array-formula-01.xls

It can be used with named ranges 'sid' and 'jim' like this:

Worksheets("Sheet1").cells(1,"A").FormulaArray = "=SUM(sid*jim)"

Patrick

Worksheets("Sheet1").cells

Patrick

Ron,

If you want to keep it all in a sub routine then you could adopt the approach shown below. It's in the attached file.

Patrick

If you want to keep it all in a sub routine then you could adopt the approach shown below. It's in the attached file.

Patrick

```
Option Explicit
Option Base 1
Sub arrXarr()
Dim arr1(6)
Dim arr2(6)
Dim varresult As Long
Dim i As Long
'populate each array with values
For i = 1 To 6
arr1(i) = i
arr2(i) = i
Next i
'multiply one array by another and sum the result step by step
For i = 1 To 6
varresult = varresult + (arr1(i) * arr2(i))
Next i
MsgBox "sum(arr1*arr2) = " & varresult
End Sub
```

array-formula-02.xls
ASKER

After a quick look, Is there a way to have:

i=1-6

arr3(1,i) = arr1(1,i) * arr2(1,i)

arr3(1,i) = arr1(1,i) * arr2(1,i)

next i

So that a new array is formed with the same 6 elements but the product of the two.

Ron

i=1-6

arr3(1,i) = arr1(1,i) * arr2(1,i)

arr3(1,i) = arr1(1,i) * arr2(1,i)

next i

So that a new array is formed with the same 6 elements but the product of the two.

Ron

Ron,

Taking your example of ranges, but using my values, the approach could be as below and in the attached file. I think this is much closer to what you are looking for.

Patrick

Taking your example of ranges, but using my values, the approach could be as below and in the attached file. I think this is much closer to what you are looking for.

Patrick

```
Option Explicit
Option Base 1
Sub arrXarr()
Dim rng1 As Range
Dim rng2 As Range
Dim celle As Range
Dim arr1(12) As Double
Dim arr2(12) As Double
Dim varresult As Double
Dim i As Long
With Sheets("Sheet1")
Set rng1 = .Range(.Cells(1, "A"), .Cells(12, "A"))
Set rng2 = .Range(.Cells(1, "C"), .Cells(12, "C"))
End With
'populate each array with values
i = 1
For Each celle In rng1
arr1(i) = celle
i = i + 1
Next celle
i = 1
For Each celle In rng2
arr2(i) = celle
i = i + 1
Next celle
'multiply one array by another and sum the result step by step
For i = 1 To 12
varresult = varresult + (arr1(i) * arr2(i))
Next i
MsgBox "sum(arr1*arr2) = " & varresult
End Sub
'Result wanted:
'varresult = Range1("A1:A12") * Range2("C1:C12")
```

array-formula-03.xls
Ron,

>So that a new array is formed with the same 6 elements but the product of the two.

Yes of course you can. See the code below and in the attached file. I have created a string of each result which is shown in MsgBox when you run the macro.

Patrick

>So that a new array is formed with the same 6 elements but the product of the two.

Yes of course you can. See the code below and in the attached file. I have created a string of each result which is shown in MsgBox when you run the macro.

Patrick

```
Option Explicit
Option Base 1
Sub arrXarr()
Dim rng1 As Range
Dim rng2 As Range
Dim celle As Range
Dim arr1(12) As Double
Dim arr2(12) As Double
Dim arr3(12) As Double
Dim varresult As Double
Dim i As Long
Dim str1 As String
With Sheets("Sheet1")
Set rng1 = .Range(.Cells(1, "A"), .Cells(12, "A"))
Set rng2 = .Range(.Cells(1, "C"), .Cells(12, "C"))
End With
'populate each array with values
i = 1
For Each celle In rng1
arr1(i) = celle
i = i + 1
Next celle
i = 1
For Each celle In rng2
arr2(i) = celle
i = i + 1
Next celle
'multiply one array by another and assign the result step by step to another array
For i = 1 To 12
arr3(i) = arr1(i) * arr2(i)
'build a string to show the results, step by step
str1 = str1 & arr3(i) & vbCrLf
Next i
MsgBox str1
End Sub
'Result wanted:
'varresult = Range1("A1:A12") * Range2("C1:C12")
```

array-formula-04.xls
Ron,

BTW the solution does not require a Variant array. Indeed it doesn't help at all and just makes the code slower to execute as the compiler needs to decide how to treat every element of a variant array.

Patrick

BTW the solution does not require a Variant array. Indeed it doesn't help at all and just makes the code slower to execute as the compiler needs to decide how to treat every element of a variant array.

Patrick

Why not just use SUMPRODUCT?

Try this:

dim varResult as long

varResult = EVALUATE("SUMPRODUCT(A1:A12,B1:B12)")

where A1:A12 is the first range, and B1:B12 is the second

-----------------------------

Or:

Sub DoSumProduct()

Dim array1 As Range, array2 As Range

Dim varResult As Double

Set array1 = Range("A1:A12")

Set array2 = Range("B1:B12")

varResult = Evaluate("Sumproduct(" & array1.Address & "," & array2.Address & ")")

MsgBox "sumproduct is " & varResult

End Sub

Dave

Try this:

dim varResult as long

varResult = EVALUATE("SUMPRODUCT(A1:A1

where A1:A12 is the first range, and B1:B12 is the second

--------------------------

Or:

Sub DoSumProduct()

Dim array1 As Range, array2 As Range

Dim varResult As Double

Set array1 = Range("A1:A12")

Set array2 = Range("B1:B12")

varResult = Evaluate("Sumproduct(" & array1.Address & "," & array2.Address & ")")

MsgBox "sumproduct is " & varResult

End Sub

Dave

But if you're working multi-dimensionals in VBA, I'd go with patrickab's very good tip

Cheers,

Dave

Cheers,

Dave

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Ron,

Once you have downloaded both of those files and tried the macros do please let me know your thoughts on the different approaches.

Patrick

Once you have downloaded both of those files and tried the macros do please let me know your thoughts on the different approaches.

Patrick

Ah, I see I wasn't paying attention - the desire was a resultant array as opposed to one value!

Cheers,

Dave

Cheers,

Dave

Dave,

In fact the issue is how to populate a series of TexBoxes on a Userform with the results of the multiplication of two arrays, placing the results in a 3rd array and using that to populate the TextBoxes. It's all part of a larger project. If you download my two files you will see what I mean.

Patrick

In fact the issue is how to populate a series of TexBoxes on a Userform with the results of the multiplication of two arrays, placing the results in a 3rd array and using that to populate the TextBoxes. It's all part of a larger project. If you download my two files you will see what I mean.

Patrick

Patrick - Will do - its grown interesting & thanks!

Dave

Dave

ASKER

Patrick,

It all works great! Exactly as I needed it to be.

Thanks,

Ron

It all works great! Exactly as I needed it to be.

Thanks,

Ron

Ron,

Thanks for the grade. Hope that it's all now working OK.

Regards

Patrick

Thanks for the grade. Hope that it's all now working OK.

Regards

Patrick

varResult.FormulaArray = "=Range1("A1:A12") * Range2("C1:C12")"

or something like that!

Patrick