Multiplying 2 arrays into a 3rd blank array range VBA(Excel)

vba populate range from array
excel vba multidimensional array
excel vba populate array with cell values
vba 2 dimensional array initialization
excel vba paste array to range
excel vba array
excel vba dynamic array
excel vba range to array one dimension
Dim i As Integer, q As Integer
Dim rng As Range
Dim my_array1elm
Dim my_array2elm
Dim x As Long

Sub Yoo()

Range("B1").Select
For i = 1 To 12
    ActiveCell.Value = i
    ActiveCell.Offset(0, 1).Select
Next

Range("A2").Select
For q = 1 To 12
    ActiveCell.Value = q
    ActiveCell.Offset(1, 0).Select
Next

my_array1 = Range("B1:M1").Select
my_array2 = Range("A2:A13").Select
my_array3 = Range("B2:M13").Select

Now I would like to multiply each elements in my_array1 with each elements in my_array2. Then populate the result(matrix) to my_array3

Having trouble to figure out looping.

Reading and writing to/from the worksheet is inefficient. Better to do your multiplying within VBA arrays, and then writing the arrays to the worksheet.

Note that the horizontal array is one-dimension, the vertical and multi-column arrays are 2D.

For example:

Option Explicit

Dim i As Integer, q As Integer
Dim rng As Range
Dim my_array1
Dim my_array2
Dim my_array3

Sub Yoo()

ReDim my_array1(1 To 12) 'horizontal array
ReDim my_array2(1 To 12, 1 To 1) 'vertical array
    For i = 1 To 12
        my_array1(i) = i
        my_array2(i, 1) = i
    Next i

ReDim my_array3(1 To 12, 1 To 12) 'results array
For i = 1 To 12
    For q = 1 To 12
        my_array3(i, q) = my_array1(i) * my_array2(q, 1)
    Next q
Next i

Cells.Clear
Range("B1:M1") = my_array1
Range("A2:A13") = my_array2
Range("B2:M13") = my_array3

End Sub

Excel VBA Arrays: Practical Example of a 2 dimensional array to , Interested to learn how to apply Excel VBA Arrays to real world examples? Learn how to Duration: 10:05 Posted: 21-Jun-2018 In excel we have an inbuilt function for matrix multiplication and it is MMULT function, it takes two arrays as an argument and returns the product of two arrays, given that both the arrays should have the same number of rows and the same number of columns.

sticking to Excel members you could use

Sub Yoo()

    Range("B1").Resize(, 12).Formula = "=COLUMN()-1"
    Range("A2").Resize(12).Formula = "=ROW()-1"
    Range("B2").Resize(12, 12).FormulaR1C1 = "=RC1*R1C"

    With Range("A1").Resize(13, 13)
        .Value = .Value
    End With

End Sub

Excel Matrix Multiplication, In excel we have an inbuilt function for matrix multiplication and it is MMULT The matrices that want to multiply have a certain number of rows and columns to All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Step 2: Select a scalar value that we are going to multiply with array i.e. 3. matrix� Each arrays’ first items will multiply against each other (e.g., 4 * 2 * 1 = 8). Then, the 2nd (4 * 2 * 2 = 16), and 3 rd, etc. Overall, this will product the array of products that look like {8, 16, 16, 32). Then the total volume would be the sum of that array, 72.

Array Multiplication Table

Change the values in the Constants section as you see fit.

The Code
Sub ArrayMultiplicationTable()

    Const cTarget As String = "A1"  ' Target First Cell Range
    Const cCol As Long = 12         ' Size of Column Source Range
    Const cRow As Long = 12         ' Size of Row Source Range

    Dim vntCol As Variant   ' Column Source Array
    Dim vntRow As Variant   ' Row Source Array
    Dim vntT As Variant     ' Target Array
    Dim i As Long           ' Row Array and Target Array Row Counter
    Dim j As Long           ' Column Array and Target Array Column Counter

    ' Redim Source Arrays
    ReDim vntCol(1 To cCol, 1 To 1)
    ReDim vntRow(1 To 1, 1 To cRow)

    ' Populate Column Source Array.
    For i = 1 To cCol
        vntCol(i, 1) = i
    Next
    ' Populate Row Source Array.
    For j = 1 To cRow
        vntRow(1, j) = j
    Next

    ' Redim Target Array.
    ReDim vntT(1 To cCol, 1 To cRow)

    ' Loop through rows of Column Source Array.
    For i = 1 To UBound(vntCol)
        ' Loop through columns of Row Source Array.
        For j = 1 To UBound(vntRow, 2)
            ' Write to Target Array.
            'vntT(i, j) = vntCol(i, 1) * vntRow(1, j)
            ' The following is a simplification of the previous line since
            ' numbers from 1 to Size of Row or Column Source Range are used
            ' as the values in the Source Arrays.
            vntT(i, j) = i * j
        Next
    Next

    ' Copy Arrays to Ranges.
    Range(cTarget).Offset(, 1).Resize(, cRow) = vntRow
    Range(cTarget).Offset(1).Resize(cCol) = vntCol
    Range(cTarget).Offset(1, 1).Resize(cCol, cRow) = vntT

End Sub

Arrays, 6.16.3 Excel formulae: inventory 3. Kind of arrays If you assign a Range to a variable its default property - Value - is Multiply every value in Range A1:A20 by 12 6.12 2-dimensional array: insert empty rows/records. In VBA, an Array is a single variable that can hold multiple values. Think of an array like a range of cells: each cell can store a value. Arrays can be one-dimensional (think of a single column), two-dimensional (think of multiple rows & columns), or multi-dimensional.

mmult result calculated by vba:

Option Explicit

Sub MatrixMultiplication()
    Dim myArr1 As Variant
    Dim myArr2 As Variant
    Dim result As Variant

    myArr1 = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
    myArr2 = Application.WorksheetFunction.Transpose(myArr1)
    Range("A2:A21") = myArr2
    Range("B1:U1") = myArr1

    result = Application.MMult(myArr2, myArr1)

    Range("B2:U21") = result

    'Range("B2:U21").FormulaArray = "=MMULT(A2:A21;B1:U1)"
    'Range("B2:U21").FormulaArray = "=MMULT(RC[-1]:R[19]C[-1],R[-1]C:R[-1]C[19])"
End Sub

Excel Cells & Ranges - Working With Arrays, It can be more efficient to load the values from a range into an array, process the data and then write it back This array is always 2 dimensional even if you are referring to a single row or column. VBA.Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) aMyArray has a value Empty if cell "A1" is empty VBA > Arrays > Transposing � Another easier way to get the character count for the range or array of items is by using the SUMPRODUCT with LEN function. You may provide a range like this: =SUMPRODUCT(LEN(A2:B3)) The Excel sheet result: You can see, the B5 cell displayed the total count of characters in A2 to B3 cells which is 17.

UPDATED: Recorded matrix multiplication with worksheet function MMULT:

Sub RecordedMatrixMultiplication()
'
' RecordedMatrixMultiplication Macro
' selects manually removed, as proposed by Ron Rosenfeld 
'
    Range("A2").Value = 1
    Range("A3").Value = 2
    Range("A2:A3").AutoFill Destination:=Range("A2:A21"), Type:=xlFillDefault
    Range("A2:A21").Copy
    Range("B1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
        SkipBlanks:= False, Transpose:=True
    Application.CutCopyMode = False
    Range("B2").FormulaR1C1 = "=MMULT(RC[-1]:R[19]C[-1],R[-1]C:R[-1]C[19])"
    Range("B2:U21").FormulaArray = "=MMULT(RC[-1]:R[19]C[-1],R[-1]C:R[-1]C[19])"
End Sub

Use array constants in array formulas, When you enter an array formula, you most often use a range of cells in your The formula multiplied A1 by 1 and B1 by 2, etc., saving you from having to put 1, 2,3,4,5 in cells In any blank cell, enter (or copy and paste) this formula, and then press If you don't select enough cells, Excel omits the values that don't have a� I am trying to simulate the behaviour of an array multiplication such as {=(A1:A10)*(B1:B10)} in VBA. The resulting array should contain: ={A1*B1, A2*B2, . . . , A10*B10}. I was hoping to avoid writing a loop if it is at all possible? I don't think there is a worksheetfunction which does this and I was hoping to avoid the loop: For i = 0 to

Learn Microsoft Excel 2002 VBA Programming with XML and ASP, However, you can append new elements to an existing array by following the ReDim This time, the values of the array's elements are obtained by multiplication: counter You can manipulate arrays with five built-in VBA functions: Array, IsArray, auto(3) auto(2) = "4-door" MsgBox auto(2)&""&auto(1) & ", " & auto(3) End� Hello, I am trying to create a formula that will multiply certain numbers within a range (1-9) by specific numbers. For example if there is a 1 in a cell, I would like that to be multiplied by 24, if there is the number 2 in a cell I would like that to be multiplied by 13.5, if the number 3 is found in a cell I would like that to be multiplied by 10, the number 4 to be multiplied by 8.5

[PDF] Basic Matrix and Vector Functions written with VBA/Excel, matlab into the excel VBA environment, to profit from built Write variant array values into a worksheet range . Create matrix with random element values – matlab: M = rand(3, 4) . Element-wise multiplication of two equal-sized matrices – matlab: M3 = M1 .* M2 . error if array is empty or not initialized, or not 2-dim. 18. Basically I'm trying to generate 2 arrays, multiply them together and sum the last (user specified ) n values. Background: I'm analysing co2 pollution, and want to model growth/expension of emissions (assuming a stratight line from one % (Emstart)to another %(Emend)), over a period of time (1 to Tn).

Excel Array Formula Examples – Simple to Advanced, Learn how to use an Excel Array Formula to do powerful calculations, Boolean Array Formulas; Sequential Number Arrays (1,2,3,. SUM-ISBLANK: Count the number of Blank values in a range A simple way to create an n x n ones matrix (J) is to multiply the identity matrix by 0 and add 1, like this: Excel includes matrix multiplication (among other matrix functions such as inverse and transposition). The function is =MMULT(), or in VBA it would be as: Application.WorksheetFunct ion.MMult(array1, array2) Note that you will not be able to assign the answer to another array - it'll have to go into a variant, e.g.: Dim myVar As Variant

Comments
  • Can you explain the end goal because I would’ve thought you could create formulas for what you want to do. It looks like you have a matrix but I’m trying to understand why you need a macro.
  • What is the purpose of all the .Select's in your code?
  • Have a look at this
  • Would MMULT serve your purpose?
  • Interesting approach. But suggest you improve it with How to avoid using select in excel vba. Also, I suggest you enter your numeric constants (1 and 2) as numbers rather than strings.
  • @Ron: as originally mentioned the code here was recorded by the macro recorder! But as I generally agree to avoid select wherever possible I have edited the code manually acc. to your proposal ;-)