Untitled

Sub UpdateLedgersWithValues()
    Dim reconWorkbook As Workbook
    Dim editedOutputWorkbook As Workbook
    Dim accountsSheet As Worksheet
    Dim ledgersSheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim ssTransactionsSheet As Worksheet
    Dim folderPath As String
    Dim folderName As String
    Dim editedOutputFilename As String
    Dim accountRow As Range
    Dim foundRow As Range
    Dim firstAddress As String
    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 matchFound As Boolean
    Dim cashValues As Collection
    Dim valueCount As Object
    Dim uninvestedCash As Variant
    Dim maxCount As Long
    Dim mostCommonValue As Variant
    Dim columnYValue As Double
    Dim columnZValue As Double
    
    ' 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")
    
    ' Check if the "ledgers" sheet exists
    On Error Resume Next
    Set ledgersSheet = ThisWorkbook.Sheets("ledgers")
    On Error GoTo 0
    
    If ledgersSheet Is Nothing Then
        MsgBox "The 'ledgers' sheet was not found!", vbCritical
        Exit Sub
    End If
    
    Set pasteSheet = editedOutputWorkbook.Sheets("SS holdings-paste from SS file")
    Set ssTransactionsSheet = editedOutputWorkbook.Sheets("Paste SS Transactions")

    ' 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 = CStr(accountRow.Offset(0, -1).Value) ' Column A, ensure it's a string
                cusip = CStr(accountRow.Offset(0, 1).Value) ' Column C, ensure it's a string
                
                ' Debugging: Output fund number
                Debug.Print "Fund Number: " & fundNumber

                ' Find the row in the Paste SS Transactions sheet for cash (without CUSIP matching)
                lastRow = ssTransactionsSheet.Cells(ssTransactionsSheet.Rows.Count, 1).End(xlUp).Row
                Set pasteRange = ssTransactionsSheet.Range("A1:Y" & lastRow)

                ' Initialize the search for FundName in the Paste SS Transactions sheet
                Set foundRow = pasteRange.Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)
                
                matchFound = False ' Reset match flag for each account
                Set cashValues = New Collection ' Collection to store uninvested cash values

                If Not foundRow Is Nothing Then
                    firstAddress = foundRow.Address ' Remember the first match to avoid looping endlessly

                    ' Loop through all matches of the FundNumber
                    Do
                        ' Ensure Column F ends with "/000" and Column G is "US DOLLAR"
                        If Right(CStr(foundRow.Offset(0, 5).Value), 4) = "/000" And CStr(foundRow.Offset(0, 6).Value) = "US DOLLAR" Then
                            ' Handle blanks by assigning 0 if the cell is empty
                            If IsEmpty(foundRow.Offset(0, 24).Value) Then
                                columnYValue = 0
                            Else
                                columnYValue = foundRow.Offset(0, 24).Value
                            End If

                            If IsEmpty(foundRow.Offset(0, 25).Value) Then
                                columnZValue = 0
                            Else
                                columnZValue = foundRow.Offset(0, 25).Value
                            End If

                            ' Debugging: Output the values found
                            Debug.Print "Found FundNumber: " & fundNumber & " | Y: " & columnYValue & " | Z: " & columnZValue & " | Y-Z: " & (columnYValue - columnZValue)

                            ' Add the adjusted value to the collection
                            cashValues.Add columnYValue - columnZValue
                            matchFound = True
                        End If
                        
                        ' Continue searching for the next occurrence of the FundNumber
                        Set foundRow = pasteRange.FindNext(foundRow)
                    Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
                End If
                
                ' Debugging: Output how many values were found
                Debug.Print "Number of matches for FundNumber " & fundNumber & ": " & cashValues.Count

                ' If matches were found, calculate the most common value from Column Y minus Column Z
                If matchFound And cashValues.Count > 0 Then
                    Set valueCount = CreateObject("Scripting.Dictionary")
                    
                    ' Count occurrences of each cash value
                    For Each uninvestedCash In cashValues
                        If valueCount.Exists(uninvestedCash) Then
                            valueCount(uninvestedCash) = valueCount(uninvestedCash) + 1
                        Else
                            valueCount.Add uninvestedCash, 1
                        End If
                    Next uninvestedCash
                    
                    ' Find the most common cash value
                    maxCount = 0
                    For Each uninvestedCash In valueCount.Keys
                        If valueCount(uninvestedCash) > maxCount Then
                            maxCount = valueCount(uninvestedCash)
                            mostCommonValue = uninvestedCash
                        End If
                    Next uninvestedCash
                    
                    ' Set the most common value in Row 3 of the ledgers sheet
                    ledgersSheet.Cells(3, accountCell.Column).Value = mostCommonValue
                    
                    ' Debugging: Output the most common uninvested cash value
                    Debug.Print "Most Common Uninvested Cash (Y-Z): " & mostCommonValue
                Else
                    ' If no match was found, leave the cell blank
                    ledgersSheet.Cells(3, accountCell.Column).Value = ""
                    Debug.Print "No valid cash values found for FundNumber: " & fundNumber
                End If
            Else
                Debug.Print "Account not found for: " & accountCell.Value
            End If
        End If
    Next accountCell

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

    MsgBox "Ledgers updated successfully!"
End Sub