varResult.FormulaArray = "=Range1("A1:A12") * Range2("C1:C12")"
or something like that!
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
array-formula-02.xls
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")
array-formula-03.xls
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")
array-formula-04.xls
If you are experiencing a similar issue, please ask a related question
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 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!