Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.
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 |
---|---|---|---|
excel formula if this do this otherwise do this | 2 | 15 | |
Word and Excel 2013 - Disabled Items List | 3 | 50 | |
Excel 2013: Default workbook | 13 | 27 | |
Excel VBA User Form Help | 21 | 26 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
13 Experts available now in Live!