I have the following Code for bank reconciliation which involves checking each cell in column D of sheet1 (bank statements) and see if it exists in column M of Sheet 2. If it doesn't flag it by saving it to arrOutput.
Being a new user, and because I could not attach the spreadsheet, I have links to what Sheet 1 and 2 look like.
Sub abc_3()
Dim i As Long, ii
Dim arrBank As Range
Dim arrAccounting As Range
Dim arrOutput
Dim temp As Variant
' setting bank transaction into range
Set bank = ActiveWorkbook.Sheets("Sheet1").Range("D25:E25" & Cells(Rows.Count, "D").End(xlUp).Row)
' setting accounting transactions into range
Set books = ActiveWorkbook.Sheets("Sheet2").Range("M1:N1" & Cells(Rows.Count, "M").End(xlUp).Row)
'everytime time the program is run arrOutput must be cleared. 3000 is an arbitrary number I chose because there will likely never be a higher number of transactions than this.
ReDim arrOutput(1 To 3000, 1 To 2)
ii = 0
' The main function of the program.. looping through every bank transaction checking if it can be found in accounting transactions,
' if it cannot be found, i.e error is thrown then save the cell to arrOutput because it needs to be flagged for checking.
' if it can be found, then ignore and check next bank transaction.
' Currently, the procedure is supposed to compare only Sheet1 credit transactions with Sheet2 credit transactions, therefore filter only credit transactions.
For Each cell In bank.Cells 'problem here is comparing both Column D and E of Sheet 1 whereas it should be comparing only column D.
If cell <> "" Then 'this is to avoid checking non-credit transactions.
On Error Resume Next
temp = Application.WorksheetFunction.VLookup(cell, books, 2, False)
If Err.Number <> 0 Then
MsgBox "Bank Transaction " & cell & " could not be found in Books Transaction history"
arrOutput(ii, 1) = cell
arrOutput(ii, 2) = ""
ii = ii + 1
End If
End If
Next
'all cells checked then dump arrOutput to range "L4" for reading
Range("l4").Resize(3000, 2) = arrOutput
bank.ClearContents
books.ClearContents
End Sub
The problem is that on every cell I get MSG "Bank Transaction " & cell & " could not be found in Books Transaction history". Consequently, Every cell gets saved to arrOutput and saved to Sheets("Sheet3").Range("L4") making me wonder whether Vlookup is not cooperating or I didn't setup the error handler correctly.
Looking forward to getting some help.. been stuck on this for too long. Thank you in advance.
1) you should qualify the ranges. 2) :E25 should be :D and :N1 should be :M. 3) Use Option Explicit and use the variables you declared (you declare some variable names but then you use other names...). 4) Finally, use Find instead of VLookup, since you only want to check the existence of the value, not a corresponding other value.
Option Explicit
Sub abc_3()
Dim bank As Range, books As Range, cell As Range
With ActiveWorkbook.Sheets("Sheet1")
Set bank = .Range("D26:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
End With
With ActiveWorkbook.Sheets("Sheet2")
Set books = .Range("M2:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
End With
Dim ii As Long, x As Range, arrOutput(1 To 3000, 1 To 2)
For Each cell In bank.Cells
If Trim(cell.Value) <> "" Then
Set x = books.Find(cell.Value, , xlValues, xlWhole)
If x Is Nothing Then
ii = ii + 1
arrOutput(ii, 1) = cell.Value
MsgBox "Bank Transaction " & cell.Value & " could not be found in Books Transaction history"
Else
x.Value = ""
End If
End If
Next
ActiveWorkbook.Sheets("Sheet3").Range("l4").Resize(3000, 2) = arrOutput
End Sub