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
Title | # Comments | Views | Activity |
---|---|---|---|
excel file | 5 | 48 | |
Automatic snapshot of data in excel triggers on a date | 3 | 21 | |
Populate data based ona criteria | 6 | 19 | |
Use cell value to specify decimal places in Excel | 9 | 12 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!