Consulting

Results 1 to 4 of 4

Thread: Sum entire column by like IDs

  1. #1

    Post Sum entire column by like IDs

    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
    Last edited by Aussiebear; 06-24-2022 at 02:29 PM. Reason: Added code tags and reduced whitespace in supplied code

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,393
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Quote Originally Posted by Paul_Hossler View Post
    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!
    Attached Files Attached Files

  4. #4
    Hi Paul,

    I figured out what was wrong and it's working!! Thank you for your help.
    Last edited by Matt27277; 06-26-2022 at 04:30 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •