Multiply a 2D array by 1D array to get a third (2D) array 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:



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
You actually don't need VBA for this but you could get the same result with an array formula.



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.