| CARVIEW |
Select Language
HTTP/2 200
date: Sat, 11 Oct 2025 06:13:49 GMT
content-type: text/html; charset=UTF-8
server: cloudflare
x-frame-options: DENY
x-content-type-options: nosniff
x-xss-protection: 1;mode=block
vary: accept-encoding
cf-cache-status: DYNAMIC
content-encoding: gzip
set-cookie: _csrf-frontend=c24bdf139f9376797d58977d7b1f05b67e33d4fc7618c622c3d2887082e793faa%3A2%3A%7Bi%3A0%3Bs%3A14%3A%22_csrf-frontend%22%3Bi%3A1%3Bs%3A32%3A%22Rj0UDgAY822f8bdas2sikRxbeyvcDp1Y%22%3B%7D; HttpOnly; Path=/
cf-ray: 98cc36b51deac16f-BLR
AUTOMATE RECONCILIATION - Pastebin.com
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub GenerateSummaryReport()
- Dim wsSupplier As Worksheet
- Dim wsNS As Worksheet
- Dim wsSummary As Worksheet
- Dim lastRowSupplier As Long
- Dim lastRowNS As Long
- Dim totalSupplierAmount As Double
- Dim totalNSAmount As Double
- Dim i As Long, j As Long
- Dim summaryRow As Long
- Dim foundMatch As Boolean
- Dim supplierAmount As Double
- Dim nsAmount As Double
- Dim discrepancyTotal As Double
- ' Set the worksheets
- Set wsSupplier = ThisWorkbook.Sheets("Supplier SOA")
- Set wsNS = ThisWorkbook.Sheets("NS SOA")
- Set wsSummary = ThisWorkbook.Sheets("Summary")
- ' Clear the Summary sheet
- wsSummary.Cells.Clear
- ' Calculate total amounts
- totalSupplierAmount = Application.WorksheetFunction.Sum(wsSupplier.Range("C2:C" & wsSupplier.Cells(wsSupplier.Rows.Count, "C").End(xlUp).Row))
- totalNSAmount = Application.WorksheetFunction.Sum(wsNS.Range("C2:C" & wsNS.Cells(wsNS.Rows.Count, "C").End(xlUp).Row))
- ' Display totals and overall difference
- wsSummary.Cells(1, 1).Value = "Total Invoice Amount of Supplier SOA"
- wsSummary.Cells(1, 2).Value = totalSupplierAmount
- wsSummary.Cells(2, 1).Value = "Total Invoice Amount of NS SOA"
- wsSummary.Cells(2, 2).Value = totalNSAmount
- wsSummary.Cells(3, 1).Value = "Overall Difference"
- wsSummary.Cells(3, 2).Value = totalNSAmount - totalSupplierAmount
- ' Initialize discrepancy total
- discrepancyTotal = 0
- ' Set header for differences table
- summaryRow = 5
- wsSummary.Cells(summaryRow, 1).Value = "Date"
- wsSummary.Cells(summaryRow, 2).Value = "Invoice Number"
- wsSummary.Cells(summaryRow, 3).Value = "Supplier Amount"
- wsSummary.Cells(summaryRow, 4).Value = "NS Amount"
- wsSummary.Cells(summaryRow, 5).Value = "Difference in Amount"
- ' Find invoices in both SOAs with amount differences
- summaryRow = 6
- For i = 2 To wsSupplier.Cells(wsSupplier.Rows.Count, "A").End(xlUp).Row
- foundMatch = False
- For j = 2 To wsNS.Cells(wsNS.Rows.Count, "A").End(xlUp).Row
- If wsSupplier.Cells(i, 2).Value = wsNS.Cells(j, 2).Value Then
- supplierAmount = Val(wsSupplier.Cells(i, 3).Value)
- nsAmount = Val(wsNS.Cells(j, 3).Value)
- If supplierAmount <> nsAmount Then
- wsSummary.Cells(summaryRow, 1).Value = wsSupplier.Cells(i, 1).Value
- wsSummary.Cells(summaryRow, 2).Value = wsSupplier.Cells(i, 2).Value
- wsSummary.Cells(summaryRow, 3).Value = supplierAmount
- wsSummary.Cells(summaryRow, 4).Value = nsAmount
- wsSummary.Cells(summaryRow, 5).Value = nsAmount - supplierAmount
- discrepancyTotal = discrepancyTotal + (nsAmount - supplierAmount)
- summaryRow = summaryRow + 1
- End If
- foundMatch = True
- Exit For
- End If
- Next j
- Next i
- ' Find invoices in Supplier SOA but not in NS SOA
- For i = 2 To wsSupplier.Cells(wsSupplier.Rows.Count, "A").End(xlUp).Row
- foundMatch = False
- For j = 2 To wsNS.Cells(wsNS.Rows.Count, "A").End(xlUp).Row
- If wsSupplier.Cells(i, 2).Value = wsNS.Cells(j, 2).Value Then
- foundMatch = True
- Exit For
- End If
- Next j
- If Not foundMatch Then
- wsSummary.Cells(summaryRow, 1).Value = wsSupplier.Cells(i, 1).Value
- wsSummary.Cells(summaryRow, 2).Value = wsSupplier.Cells(i, 2).Value
- wsSummary.Cells(summaryRow, 3).Value = Val(wsSupplier.Cells(i, 3).Value)
- wsSummary.Cells(summaryRow, 4).Value = "Not in NS SOA"
- wsSummary.Cells(summaryRow, 5).Value = Val(wsSupplier.Cells(i, 3).Value) ' Since NS Amount is missing
- discrepancyTotal = discrepancyTotal + Val(wsSupplier.Cells(i, 3).Value)
- summaryRow = summaryRow + 1
- End If
- Next i
- ' Find invoices in NS SOA but not in Supplier SOA
- For j = 2 To wsNS.Cells(wsNS.Rows.Count, "A").End(xlUp).Row
- foundMatch = False
- For i = 2 To wsSupplier.Cells(wsSupplier.Rows.Count, "A").End(xlUp).Row
- If wsNS.Cells(j, 2).Value = wsSupplier.Cells(i, 2).Value Then
- foundMatch = True
- Exit For
- End If
- Next i
- If Not foundMatch Then
- wsSummary.Cells(summaryRow, 1).Value = wsNS.Cells(j, 1).Value
- wsSummary.Cells(summaryRow, 2).Value = wsNS.Cells(j, 2).Value
- wsSummary.Cells(summaryRow, 3).Value = "Not in Supplier SOA"
- wsSummary.Cells(summaryRow, 4).Value = Val(wsNS.Cells(j, 3).Value)
- wsSummary.Cells(summaryRow, 5).Value = Val(wsNS.Cells(j, 3).Value) ' Since Supplier Amount is missing
- discrepancyTotal = discrepancyTotal + Val(wsNS.Cells(j, 3).Value)
- summaryRow = summaryRow + 1
- End If
- Next
- End Sub
Tags:
Vba code
Advertisement
Add Comment
Please, Sign In to add comment
-
⭐✅ Marketplace Glitch ✅ Working ✅ NEVER SEEN...
JavaScript | 2 sec ago | 0.24 KB
-
✅⭐ Make $2500 in 15 minutes ✅ NEVER SEEN BEFO...
JavaScript | 11 sec ago | 0.24 KB
-
⭐ Instant BTC Profit Method ✅ NEVER SEEN BEFO...
JavaScript | 24 sec ago | 0.24 KB
-
⭐ Instant BTC Profit Method ✅ NEVER SEEN BEFO...
JavaScript | 31 sec ago | 0.24 KB
-
⭐✅ MAKE $2000 INSTANTLY ✅ NEVER SEEN BEFORE ⭐...
JavaScript | 40 sec ago | 0.24 KB
-
⭐ Free Crypto Method ✅ NEVER SEEN BEFORE ⭐⭐⭐
JavaScript | 49 sec ago | 0.24 KB
-
✅⭐ Make huge profits on trading ✅ NEVER SEEN...
JavaScript | 59 sec ago | 0.24 KB
-
⭐✅ Marketplace Glitch ✅ Working ✅ NEVER SEEN...
JavaScript | 1 min ago | 0.24 KB
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
