Untitled

Sub UpdateRow8WithPortiaValues()
    Dim accountsSheet As Worksheet
    Dim ledgersSheet As Worksheet
    Dim portiaWorkbook As Workbook
    Dim portiaSheet As Worksheet
    Dim folderPath As String
    Dim portiaFilename As String
    Dim lastCol As Long
    Dim accountCell As Range
    Dim fundNumber As String
    Dim foundRow As Range
    Dim firstAddress As String
    Dim lastRow As Long
    Dim portiaValue As Variant
    
    ' Set folder path
    folderPath = ThisWorkbook.Path & "\"

    ' Construct the Portia filename (ensure it matches the format yyyy.mmdd)
    portiaFilename = folderPath & Format(Date, "yyyy.mmdd") & ".Portia Settled Cash Balances.xlsx"

    ' Open the Portia workbook
    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 accountsSheet = ThisWorkbook.Sheets("accounts")
    Set ledgersSheet = ThisWorkbook.Sheets("ledgers")
    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
                
                ' Debugging: Output fund number
                Debug.Print "Fund Number: " & fundNumber

                ' Find the row in the Portia sheet for the portia value
                lastRow = portiaSheet.Cells(portiaSheet.Rows.Count, 1).End(xlUp).Row
                Set foundRow = portiaSheet.Range("A1:C" & lastRow).Find(What:=fundNumber, LookIn:=xlValues, LookAt:=xlWhole)

                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
                        ' Check if Column B contains '-CASH-'
                        If CStr(foundRow.Offset(0, 1).Value) Like "*-CASH-*" Then
                            portiaValue = foundRow.Offset(0, 2).Value ' Column C for balance
                            ledgersSheet.Cells(8, accountCell.Column).Value = portiaValue
                            
                            ' Debugging: Output the retrieved Portia value
                            Debug.Print "Portia Value: " & portiaValue
                        End If
                        
                        ' Continue searching for the next occurrence of the FundNumber
                        Set foundRow = portiaSheet.Range("A1:C" & lastRow).FindNext(foundRow)
                    Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress
                End If
            End If
        End If
    Next accountCell

    ' Close the Portia workbook
    portiaWorkbook.Close SaveChanges:=False

    MsgBox "Row 8 updated with Portia values successfully!"
End Sub