Untitled

Sub UpdateLedgersWithValues()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim accountsSheet As Worksheet
    Dim ledgersSheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim folderPath As String
    Dim folderName As String
    Dim editedOutputFilename As String
    Dim accountRow As Range
    Dim lastCol As Long
    Dim accountCell As Range
    Dim fundNumber As String
    Dim cusip As String
    Dim rValue As Variant
    Dim pasteRange As Range
    Dim lastRow As Long
    Dim yValues As Collection
    Dim zValues As Collection
    Dim i As Long

    ' Set folder path and extract folder name
    folderPath = ThisWorkbook.Path & "\"
    folderName = Mid(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") + 1)

    ' Construct the edited output filename
    editedOutputFilename = folderPath & folderName & ".SS Daily Cash Transactions_Edited_Output.xlsx"

    ' Open the Edited Output workbook
    Set editedOutputWorkbook = Workbooks.Open(editedOutputFilename)
    Set accountsSheet = ThisWorkbook.Sheets("accounts")
    Set ledgersSheet = ThisWorkbook.Sheets("ledgers")
    Set pasteSheet = editedOutputWorkbook.Sheets("SS holdings-paste from SS file")

    ' Find the last column in Row 1 of the ledgers sheet
    lastCol = ledgersSheet.Cells(1, ledgersSheet.Columns.Count).End(xlToLeft).Column

    ' Loop through each account in Row 1
    For Each accountCell In ledgersSheet.Range("B1:" & ledgersSheet.Cells(1, lastCol).Address)
        If accountCell.Value <> "" Then
            ' Find account in the accounts sheet
            Set accountRow = accountsSheet.Columns("B").Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole)

            If Not accountRow Is Nothing Then
                fundNumber = accountRow.Offset(0, -1).Value ' Column A
                cusip = accountRow.Offset(0, 1).Value ' Column C
                
                ' Debugging: Output fund number and CUSIP
                Debug.Print "Fund Number: " & fundNumber & ", CUSIP: " & cusip

                ' Find the row in the Paste SS Transactions sheet
                lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
                Set pasteRange = pasteSheet.Range("A1:R" & lastRow)

                ' Logic to collect invested cash amounts for row 2
                Set accountRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
                If Not accountRow Is Nothing Then
                    If accountRow.Offset(0, 10).Value = cusip Then ' Column K
                        If Not IsError(accountRow.Offset(0, 17).Value) Then ' Column R
                            rValue = accountRow.Offset(0, 17).Value ' Column R
                            ledgersSheet.Cells(2, accountCell.Column).Value = rValue
                            Debug.Print "Row 2 R Value: " & rValue
                        Else
                            ledgersSheet.Cells(2, accountCell.Column).Value = "Error"
                            Debug.Print "Error in Row 2 R Value for Fund Number: " & fundNumber
                        End If
                    End If
                End If

                ' Logic to collect uninvested cash amounts for row 3
                Set yValues = New Collection
                Set zValues = New Collection
                
                ' Iterate over each row in the Paste SS Transactions sheet
                For i = 2 To lastRow ' Assuming row 1 is headers
                    If pasteRange.Cells(i, 1).Value = fundNumber And _
                       Right(pasteRange.Cells(i, 6).Value, 4) = "/000" And _
                       pasteRange.Cells(i, 7).Value = "US DOLLAR" Then ' Columns F and G

                        If Not IsEmpty(pasteRange.Cells(i, 25).Value) Then ' Column Y
                            yValues.Add pasteRange.Cells(i, 25).Value
                        ElseIf Not IsEmpty(pasteRange.Cells(i, 26).Value) Then ' Column Z
                            zValues.Add -pasteRange.Cells(i, 26).Value ' Treat Z as negative
                        End If
                    End If
                Next i

                ' Determine the most common Y value if available
                If yValues.Count > 0 Then
                    rValue = GetMostCommonValue(yValues)
                ElseIf zValues.Count > 0 Then
                    rValue = GetMostCommonValue(zValues)
                Else
                    rValue = 0 ' No values found
                End If

                ' Assign uninvested cash value to row 3
                ledgersSheet.Cells(3, accountCell.Column).Value = rValue

                ' Logic to collect portia values for row 8
                Set accountRow = accountsSheet.Columns("B").Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole)
                If Not accountRow Is Nothing Then
                    fundNumber = accountRow.Offset(0, -1).Value ' Column A
                    
                    ' Open the Portia Settled Cash Balances workbook
                    Dim portiaFilename As String
                    portiaFilename = folderPath & folderName & ".Portia Settled Cash Balances.xlsx"
                    Dim portiaWorkbook As Workbook
                    Set portiaWorkbook = Workbooks.Open(portiaFilename)
                    Dim portiaSheet As Worksheet
                    Set portiaSheet = portiaWorkbook.Sheets("prior day settled cash")

                    ' Find the last row in the Portia sheet
                    lastRow = portiaSheet.Cells(portiaSheet.Rows.Count, 1).End(xlUp).Row
                    Set pasteRange = portiaSheet.Range("A1:C" & lastRow)

                    ' Find portia value
                    Set accountRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
                    If Not accountRow Is Nothing Then
                        If accountRow.Offset(0, 1).Value = "-CASH-" Then ' Column B
                            rValue = accountRow.Offset(0, 2).Value ' Column C
                            ledgersSheet.Cells(8, accountCell.Column).Value = rValue
                            Debug.Print "Row 8 Portia Value: " & rValue
                        End If
                    End If
                    
                    ' Close the Portia workbook
                    portiaWorkbook.Close SaveChanges:=False
                End If
            Else
                Debug.Print "Account not found for: " & accountCell.Value
            End If
        End If
    Next accountCell

    ' Sum values for rows 2 and 3 into row 4
    For accountCell In ledgersSheet.Range("B1:" & ledgersSheet.Cells(1, lastCol).Address)
        If accountCell.Value <> "" Then
            Dim row2Value As Variant
            Dim row3Value As Variant
            
            row2Value = ledgersSheet.Cells(2, accountCell.Column).Value
            row3Value = ledgersSheet.Cells(3, accountCell.Column).Value
            
            ' Handle blanks as 0
            If IsEmpty(row2Value) Then row2Value = 0
            If IsEmpty(row3Value) Then row3Value = 0
            
            ledgersSheet.Cells(4, accountCell.Column).Value = row2Value + row3Value
        End If
    Next accountCell

    ' Close the Edited Output workbook
    editedOutputWorkbook.Close SaveChanges:=False

    MsgBox "Ledgers updated successfully!"
End Sub

Function GetMostCommonValue(col As Collection) As Variant
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    Dim item As Variant
    
    ' Count occurrences of each item
    For Each item In col
        If dict.Exists(item) Then
            dict(item) = dict(item) + 1
        Else
            dict.Add item, 1
        End If
    Next item
    
    ' Find the most common item
    Dim mostCommonValue As Variant
    Dim maxCount As Long
    maxCount = 0
    
    For Each item In dict.Keys
        If dict(item) > maxCount Then
            mostCommonValue = item
            maxCount = dict(item)
        End If
    Next item
    
    GetMostCommonValue = mostCommonValue
End Function