CODEWITHSUNDEEP
excelvba
Chris2015
Chris2015

Reputation: 1050

Multiplying Two Arrays in Excel VBA

I am trying to multiply two arrays placing the value of each iteration into a spreadsheet. Here is what I have so far:

Sub Test1()
Dim x As Long
Dim myArray
Dim myArrayAdj

myArray = Array(24800, 26300, 27900)
myArrayAdj = Array(1.0025, 1.005, 1.0075, 1.01)
For x = 1 To 1000
Cells(x, x) = myArray * myArrayAdj
Next x

End Sub

When I run this I get a Run-time13 error with the following highlighted:

Cells(x, x) = myArray * myArrayAdj

Can someone explain to me where I've gone wrong? Thank you!

Upvotes: 1

Views: 20725

Answers (2)

ARich
ARich

Reputation: 3279

Your have a few issues which I think I've corrected below. One of the things you'll notice about my code is that I use Variant variables to loop through the arrays instead of identifying the element by number (e.g. myArrayElm instead of myArray(x)). This is just my personal preference.

Sub Test1()
Dim x As Long
Dim myArray         'Your first array
Dim myArrayElm      'A variable for the elements in your first array
Dim myArrayAdj      'Your second array
Dim myArrayAdjElm   'A variable for the elements in your second array

'Add values to your arrays
myArray = Array(24800, 26300, 27900)
myArrayAdj = Array(1.0025, 1.005, 1.0075, 1.01)

'Loop through the elements in your first array
For Each myArrayElm In myArray
    'Loop through the elements in your second array
    For Each myArrayAdjElm In myArrayAdj
        x = x + 1
        'Multiply the two array elements together
        Cells(x, 1) = myArrayElm * myArrayAdjElm
    Next myArrayAdjElm
Next myArrayElm

End Sub

This code loops through each element in both arrays, multiplies the two elements, and stores the values in a list beginning in cell A1.

Now, if you have a large dataset you're working with, the below example will be more efficient and will finish quicker since it stores the results in another array and then pastes the results to a sheet all at once instead of individually:

Option Base 1

Sub Test1()
Dim x As Long
Dim myArray         'Your first array
Dim myArrayElm      'A variable for the elements in your first array
Dim myArrayAdj      'Your second array
Dim myArrayAdjElm   'A variable for the elements in your second array
Dim Results         'An array for your results
Dim r As Range      'Range to store values

'Add values to your arrays
myArray = Array(24800, 26300, 27900)
myArrayAdj = Array(1.0025, 1.005, 1.0075, 1.01)

'Set the size of the results array
ReDim Results(1 To UBound(myArray) * UBound(myArrayAdj))

'Loop through the elements in your first array
For Each myArrayElm In myArray
    'Loop through the elements in your second array
    For Each myArrayAdjElm In myArrayAdj
        x = x + 1
        'Multiply the two array elements together
        Results(x) = myArrayElm * myArrayAdjElm
    Next myArrayAdjElm
Next myArrayElm

'Set the destination range
Set r = Range("A1:A" & UBound(Results))

'Paste results to sheet
r = Application.Transpose(Results)

End Sub

Note the Option Base 1 at the top. This just means that all the arrays will now start at element 1 instead of element 0 which is the default.

Upvotes: 1

Mike Dinescu
Mike Dinescu

Reputation: 55720

The whole problem stems from your statement right here: "I want to multiply two arrays"

I assume by that you meant you would like to multiple the individual elements in the two arrays, one by one.

In that case you want to do something this:

 Cells(x, x) = myArray(x) * myArrayAdj(x)

That said, I'm not sure whether your intention was to store the results of the multiplication in cells on the diagonal of the work sheet or in some other place

If it's the former then Cells(x,x) makes sense but if it's the latter, than you need to be more specific about your expectation in multiplying the two arrays.

Upvotes: 0

Related Questions