Multiply a 2D array by 1D array to get a third (2D) array

Question

I've working with three dynamic arrays (all are datatype Double) - they are

OriningalArray

This will be assigned from a range that the end user will see and will be 2 dimension

MultiplierArray

This will be multipliers as (most of which will be 1 but some will be between +-5% and will always be same length as one of the dimensions in OriningalArray.

NewArray

This is required as there will be certain discounts that will need to be applied to the OriningalArray and both dimension will be the same size as it.

Here's a sample for a visual reference:

enter image description here

I have code that works (below) and have commented it also to explain why I'm doing it that way (this is just an example and actual data size will be much bigger) but was hoping someone could tell me how to optimize it further:

Sub Test()
Dim OriningalArray() As Double ' I can't declare it a Variant and then assign it straight from the range (OriningalArray = Rng) because there may be "N/A" values in the range which, when put into an Variant Array, gives false Double value
Dim MultiplierArray() As Variant
Dim NewArray() As Double
Dim Rng As Range
Dim MultiplierRng As Range
Dim x As Long, y As Long
Set Rng = Range("D4:I9")
Set MultiplierRng = Range("D12:I12")
ReDim OriningalArray(1 To Rng.Rows.Count, 1 To Rng.Columns.Count) ' 2D Array the sze of the range
ReDim NewArray(1 To Rng.Rows.Count, 1 To Rng.Columns.Count) ' 2D Array the sze of the range
MultiplierArray = MultiplierRng
On Error Resume Next ' Turn off error handler to stop macro crashing when trying to assign "N/A" as a Double
For x = 1 To Rng.Columns.Count
 For y = 1 To Rng.Rows.Count
 OriningalArray(y, x) = Rng.Cells(y, x).Value
 NewArray(y, x) = OriningalArray(y, x) * MultiplierRng(1, x)
 'Debug.Print OriningalArray(y, x)
 'Debug.Print NewArray(y, x)
 Next y
Next x
On Error GoTo 0
End Sub

Answer 1

You actually don't need VBA for this but you could get the same result with an array formula.

screenshot

You select the cells D8:F9 so that the D8 is active. Then you type the following formula there

=D6:F6*D3:F4

and hit CTRL+SHIFT+ENTER

if it worked you'll see {} around each formula in each cell and the results in the second table.

• Jeremy: Thanks but I need it in a larger VBA module (sorry, I should have clarified this) - Found a solution to this (see my answer)

Answer 2

Problem solved:

First, assign OriningalArray in one step:

OriningalArray = Range("D4:I9").Value2

Then, assign the NewArray using the evaluate method (no need for a MultiplierArray):

NewArray = Activesheet.Evaluate("D4:I9*D12:I12")

Note: NewArray must be declared as a Variantdata type

• t3chb0t: Nice. This is good to know. Thx for posting ;-)