Sum entire column by like IDs

[Matt27277]

29882

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

[Paul_Hossler]

I have attached a screenshot as reference

1. A small example workbook with some data and the macros makes it easier for people to see

2. The way PortKeys is Dim-ed and used, I'd guess that it's an object and belonging to the Portfolios collection

In the original workbook, was there a Class module called cItems? Add that to the 64 bit version and see if it works



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

[Matt27277]

1. A small example workbook with some data and the macros makes it easier for people to see

2. The way PortKeys is Dim-ed and used, I'd guess that it's an object and belonging to the Portfolios collection

In the original workbook, was there a Class module called cItems? Add that to the 64 bit version and see if it works



Hi Paul,
Appreciate the feedback so quickly! I dug a little deeper into the coding and I did find a Class module called cItems. I tried to copy/paste and mimic as much as possible, including the Forms folder. The Forms is unnecessary, as long as the sum function works!

I am now getting a "Run-time error '13': Type mismatch for the line: "MV = LoadMeSht.Range("G" & counter).Value"

I've attached an example workbook as mentioned! I hope this helps!

[Matt27277]

Hi Paul,

I figured out what was wrong and it's working!! Thank you for your help.