Untitled
Greg
plain_text
6 months ago
3.2 kB
5
Indexable
' Now loop through each account number in Column B of accounts sheet For Each accountRow In accountsSheet.Range("B2:B" & lastRow) accountNumber = accountRow.Value ' Open the Portia transactions workbook Set portiaTransactionsWorkbook = Workbooks.Open(portiaTransactionsFilename) Set portiaTransactionsSheet = portiaTransactionsWorkbook.Sheets(1) ' Adjust the sheet index if necessary ' Find the account number in the Portia transactions sheet lastRow = portiaTransactionsSheet.Cells(portiaTransactionsSheet.Rows.Count, 1).End(xlUp).Row Set foundRow = portiaTransactionsSheet.Range("A1:A" & lastRow).Find(What:=accountNumber, LookIn:=xlValues, LookAt:=xlWhole) If Not foundRow Is Nothing Then firstAddress = foundRow.Address ' Save the first address to avoid endless loops Do ' Check if Column F is "-CASH-" and that there is a value in W or V before pasting If foundRow.Offset(0, 5).Value = "-CASH-" Then ' Column F valueW = foundRow.Offset(0, 22).Value ' Column W valueV = foundRow.Offset(0, 21).Value ' Column V If Not IsEmpty(valueW) Or Not IsEmpty(valueV) Then ' Update value of G by adding an apostrophe updatedGValue = "'" & foundRow.Offset(0, 6).Value ' Column G with an apostrophe ' Check if the updated value of G matches the cash CUSIP, if so, exclude If updatedGValue <> cashCUSIP Then ' Paste values into the transactions sheet without formatting With transactionsSheet .Cells(transactionsRow, 1).Value = foundRow.Offset(0, 0).Value ' Column A .Cells(transactionsRow, 2).Value = "Reams" ' Set "Reams" as Source for Column B .Cells(transactionsRow, 3).Value = foundRow.Offset(0, 2).Value ' Date (Column C from Portia) .Cells(transactionsRow, 4).Value = foundRow.Offset(0, 3).Value ' CUSIP (Column D from Portia) .Cells(transactionsRow, 5).Value = updatedGValue ' Description (Column G from Portia) .Cells(transactionsRow, 6).Value = foundRow.Offset(0, 21).Value ' Amount (Column V from Portia) .Cells(transactionsRow, 7).Value = foundRow.Offset(0, 31).Value ' Principal (Column AF from Portia) .Cells(transactionsRow, 8).Value = foundRow.Offset(0, 32).Value ' Interest (Column AG from Portia) End With transactionsRow = transactionsRow + 1 ' Move to the next row in transactions sheet End If End If End If ' Continue searching for the next occurrence of the AccountNumber Set foundRow = portiaTransactionsSheet.Range("A1:A" & lastRow).FindNext(foundRow) Loop While Not foundRow Is Nothing And foundRow.Address <> firstAddress End If portiaTransactionsWorkbook.Close False ' Close the Portia transactions workbook Next accountRow
Editor is loading...
Leave a Comment