Untitled

Sub UpdateLedgersWithValues()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim accountsSheet As Worksheet
    Dim ledgersSheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim portiaWorkbook As Workbook
    Dim portiaSheet As Worksheet
    Dim folderPath As String
    Dim folderName As String
    Dim editedOutputFilename As String
    Dim portiaFilename As String
    Dim accountCell As Range
    Dim lastCol As Long
    Dim fundNumber As String
    Dim cusip As String
    Dim rValue As Variant
    Dim pasteRange As Range
    Dim lastRow As Long
    Dim foundRow As Range
    Dim firstAddress As String
    Dim portiaValue As Variant
    Dim accountRow As Range
    Dim yValues As Collection
    Dim value As Variant
    Dim maxCount As Long
    Dim mostCommonValue As Variant

    ' 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")

    ' Open the Portia workbook
    portiaFilename = folderPath & folderName & ".Portia Settled Cash Balances.xlsx"
    On Error Resume Next
    Set portiaWorkbook = Workbooks.Open(portiaFilename)
    On Error GoTo 0

    If portiaWorkbook Is Nothing Then
        MsgBox "The Portia Settled Cash Balances file was not found!", vbCritical
        Exit Sub
    End If

    Set portiaSheet = portiaWorkbook.Sheets("prior day settled cash")

    ' 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

                ' Update Row 2 (Invested Cash)
                lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, 1).End(xlUp).Row
                Set pasteRange = pasteSheet.Range("A1:R" & lastRow)
                Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)

                If Not foundRow Is Nothing Then
                    ' Get values for invested cash from pasteSheet
                    If foundRow.Offset(0, 10).Value = cusip Then ' Column K
                        If Not IsError(foundRow.Offset(0, 17).Value) Then ' Column R
                            rValue = foundRow.Offset(0, 17).Value
                            ledgersSheet.Cells(2, accountCell.Column).Value = rValue
                            Debug.Print "Row 2 - R Value: " & rValue
                        Else
                            ledgersSheet.Cells(2, accountCell.Column).Value = "Error"
                            Debug.Print "Row 2 - Error in R Value for Fund Number: " & fundNumber
                        End If
                    Else
                        Debug.Print "Row 2 - CUSIP mismatch for Fund Number: " & fundNumber
                    End If
                Else
                    Debug.Print "Row 2 - Fund Number not found in Paste Sheet: " & fundNumber
                End If

                ' Update Row 3 (Uninvested Cash)
                Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
                Set yValues = New Collection

                If Not foundRow Is Nothing Then
                    firstAddress = foundRow.Address
                    Do
                        If Right(foundRow.Offset(0, 5).Value, 4) = "/000" And foundRow.Offset(0, 6).Value = "US DOLLAR" Then
                            Dim cashValue As Double
                            If IsNumeric(foundRow.Offset(0, 24).Value) Then
                                cashValue = foundRow.Offset(0, 24).Value
                            Else
                                cashValue = 0 ' Consider blank as 0
                            End If
                            yValues.Add cashValue ' Add Column Y directly
                        End If
                        Set foundRow = pasteRange.FindNext(foundRow)
                    Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
                End If

                ' Calculate the most common value for Row 3
                If yValues.Count > 0 Then
                    Dim dict As Object
                    Set dict = CreateObject("Scripting.Dictionary")

                    For Each value In yValues
                        If Not dict.Exists(value) Then
                            dict(value) = 1
                        Else
                            dict(value) = dict(value) + 1
                        End If
                    Next value

                    ' Find the most common value
                    For Each value In dict.Keys
                        If dict(value) > maxCount Then
                            maxCount = dict(value)
                            mostCommonValue = value
                        End If
                    Next value

                    ledgersSheet.Cells(3, accountCell.Column).Value = mostCommonValue
                Else
                    ledgersSheet.Cells(3, accountCell.Column).Value = 0 ' Default to 0 if no matches
                End If

                ' Update Row 8 (Portia Values)
                lastRow = portiaSheet.Cells(portiaSheet.Rows.Count, 1).End(xlUp).Row
                Set foundRow = portiaSheet.Range("A1:C" & lastRow).Find(What:=accountCell.Value, LookIn:=xlValues, LookAt:=xlWhole) ' Match account number

                If Not foundRow Is Nothing Then
                    firstAddress = foundRow.Address
                    Do
                        If CStr(foundRow.Offset(0, 1).Value) Like "*-CASH-*" Then
                            portiaValue = foundRow.Offset(0, 2).Value ' Column C
                            ledgersSheet.Cells(8, accountCell.Column).Value = portiaValue
                            Debug.Print "Row 8 - Portia Value: " & portiaValue
                        End If
                        Set foundRow = portiaSheet.Range("A1:C" & lastRow).FindNext(foundRow)
                    Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
                Else
                    Debug.Print "Row 8 - Account Number not found in Portia Sheet: " & accountCell.Value
                End If
            Else
                Debug.Print "Account not found for: " & accountCell.Value
            End If
        End If
    Next accountCell

    ' Close the workbooks
    editedOutputWorkbook.Close SaveChanges:=False
    portiaWorkbook.Close SaveChanges:=False

    MsgBox "Ledgers updated successfully!"
End Sub