OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 426
- Office Version
- 2019
- Platform
- Windows
Thanks in advance for your assistance. Actually, it doesn't have to be the method I have proposed with sample code as I am just looking for the most efficient and fastest method.
What is the VBA Code to read in two columns into two respective Scripting Dictionaries and then perform an operation such as multiplication. The following is code I have written thus far, but it gives me nothing for the output and I have also done a test where I read in the values from columns 5 and 6 and put them into columns 7 and 8 respectively, but the cells are in columns 7 and 8 show up blank which leads me to believe they are not being read into their respective dictionaries.
Sample data set. Column G has the final values I am trying to calc.
Code:
What is the VBA Code to read in two columns into two respective Scripting Dictionaries and then perform an operation such as multiplication. The following is code I have written thus far, but it gives me nothing for the output and I have also done a test where I read in the values from columns 5 and 6 and put them into columns 7 and 8 respectively, but the cells are in columns 7 and 8 show up blank which leads me to believe they are not being read into their respective dictionaries.
Sample data set. Column G has the final values I am trying to calc.
Tickers, Missing - (2022-04-01, V01) - 1 of .csv | |||||
---|---|---|---|---|---|
E | F | G | |||
8 | Price | Quantity | Total | ||
9 | $3.00 | 2 | $6.00 | ||
10 | $4.00 | 10 | $40.00 | ||
11 | $4.50 | 11 | $49.50 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G9:G11 | G9 | =E9*F9 |
Code:
VBA Code:
Sub TestDictOperation()
'Dimensioning
Dim i As Long, LastRow As Long
Dim ShtNm As String
Dim Dict1 As Object, Dict2 As Object, Dict3 As Object
'Set sheet
ShtNm = ActiveSheet.name
'Code - find last row
With Sheets(ShtNm)
LastRow = .Cells.Find(What:="*", AFTER:=.Cells(1), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With
'Set dictionaries
Set Dict1 = CreateObject("Scripting.Dictionary")
Set Dict2 = CreateObject("Scripting.Dictionary")
Set Dict3 = CreateObject("Scripting.Dictionary")
'Read in values and if both of the cells were not blank, then multiply and output
With Sheets(ShtNm)
For i = 9 To LastRow
Dict1(.Cells(i, 5).Value) = Empty
Dict2(.Cells(i, 6).Value) = Empty
If Dict1(.Cells(i, 5).Value) <> "" And Dict2(.Cells(i, 6).Value) <> "" Then
.Cells(i, 7) = Dict1(.Cells(i, 5).Value) * Dict2(.Cells(i, 6).Value)
End If
Next i
End With
End Sub