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 uninvestedCash 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")
    
    ' 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 and CUSIP
                Debug.Print "Fund Number: " & fundNumber & ", CUSIP: " & cusip

                ' Find the row in the Paste SS Transactions sheet for cash (new logic)
                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
                uninvestedCash = 0 ' Initialize uninvested cash variable

                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 I is "USD"
                        If Right(CStr(foundRow.Offset(0, 5).Value), 4) = "/000" And foundRow.Offset(0, 8).Value = "USD" Then
                            ' Calculate uninvested cash (Column Y - Column Z)
                            If Not IsError(foundRow.Offset(0, 24).Value) And Not IsError(foundRow.Offset(0, 25).Value) Then
                                uninvestedCash = foundRow.Offset(0, 24).Value - foundRow.Offset(0, 25).Value
                                ledgersSheet.Cells(3, accountCell.Column).Value = uninvestedCash
                                
                                ' Debugging: Output the uninvested cash value
                                Debug.Print "Uninvested Cash: " & uninvestedCash
                                
                                matchFound = True ' Set flag if a match is found
                                Exit Do ' Exit loop once a match is found
                            End If
                        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
                
                ' If no match was found at all, leave the cell blank
                If Not matchFound Then
                    ledgersSheet.Cells(3, accountCell.Column).Value = ""
                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