Solved

Multiplying an Array by another Array

Posted on 2011-02-11
20
213 Views
Last Modified: 2012-08-14
I have a Range 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.
0
Comment
Question by:Cook09
  • 14
  • 4
  • 2
20 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 34874871
Ron,

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

or something like that!

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34874885
It's assigned to cells like this:

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


0
 
LVL 45

Expert Comment

by:patrickab
ID: 34874896
By using FormulaArray it converts an ordinary formula into an array formula and in so doing adds the curly brackets.

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34874900
But then no doubt someone else will correct me...
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34874953
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34875000
It can be used with named ranges 'sid' and 'jim' like this:

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

Patrick
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34875250
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
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

Open in new window

array-formula-02.xls
0
 

Author Comment

by:Cook09
ID: 34875299
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
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34875399
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
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")

Open in new window

array-formula-03.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34875470
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
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")

Open in new window

array-formula-04.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 45

Expert Comment

by:patrickab
ID: 34875678
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 34881551
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
0
 
LVL 41

Expert Comment

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

Cheers,

Dave
0
 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 34882177
Ron,

The attached two files show different ways either of which hopefully will help in your project. They are very different approaches to different arrays.

Patrick
splitting-into-arrays-01.xls
ron-06.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34882178
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
0
 
LVL 41

Expert Comment

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

Cheers,

Dave
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34882214
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

0
 
LVL 41

Expert Comment

by:dlmille
ID: 34882244
Patrick - Will do - its grown interesting & thanks!

Dave
0
 

Author Closing Comment

by:Cook09
ID: 34888776
Patrick,

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

Thanks,

Ron
0
 
LVL 45

Expert Comment

by:patrickab
ID: 34888818
Ron,

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

Regards

Patrick
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Unable to open excel in 2016 is slow 4 21
location range 4 22
Adding Text that self adjusts in a Cell 8 33
Auto Adjust Percent rate 5 30
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now