example file.jpg
Hi,
My work used to use a VBA code on a 32-bit excel and we no longer use 32-bit as we have moved to 64-bit. I'm trying to only mimic one command within the old VBA coding to the new 64-bit but I have no VBA experience. I tried to copy the declarations and the script for the one command I need, which is below. Every time I try to run the code, it will say "Compile Error: User-defined type not defined" and it hightlights the line "Dim PortKeys as cItems". I have no idea what this cItems is and even if I remove the two lines that refer to cItems, it says "Subscript out of range".
What I am trying to accomplish, is within column B, we have product IDs, for example, ABC, DEF, GHI, etc. and we want to sum their values that are in column G, based off their product IDs. Within these product IDs, there are multiple items within them with different values in column G. For example, ABC will have 5 items and DEF will have 7. I want the code to sum the 5 ABC items' values and return its total value within a message box, along with the total value of DEF. Or to make it possibly easier, to output the sum in column H. I have attached a screenshot as reference.
Public Key As String
Public sum As Double
Public Count As Long
Public Portfolios As Collection
Sub PrdTtlMV()
Dim Portfolios As Collection
Dim PortKeys as cItems
Dim PortName As String, AllMktVals As String
Dim MV As Double, counter As Long, lRow As Long
Dim LoadMeSht As Worksheet, SpacesNeeded As Variant
Set Portfolios = New Collection
Set LoadMeSht = Workbooks("load_me_test.csv").Sheets(1)
lRow = LoadMeSht.Cells(Rows.Count, "B").End(xlUp).Row
For counter = 1 To lRow
PortName = CStr(LoadMeSht.Range("B" & counter).Value)
MV = LoadMeSht.Range("G" & counter).Value
Set PortKeys = Nothing: On Error Resume Next
Set PortKeys = Portfolios(PortName): On Error GoTo 0
If PortKeys Is Nothing Then
Set PortKeys = New cItems
PortKeys.Key = PortName
Portfolios.Add PortKeys, PortName
End If
With PortKeys
.sum = .sum + MV
.Count = .Count + 1
.Portfolios.Add MV
End With
Next counter
For Each PortKeys In Portfolios
AllMktVals = AllMktVals & PortKeys.Key & SpacesNeeded & " = " & FormatCurrency(PortKeys.sum) & vbNewLine
Next PortKeys
MsgBox AllMktVals
End Sub