Vlookup doesn't seem to cooperate

Question

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.

Sheet1 Sheet2

 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.

Add Option Explicit to the top row of your module. Get rid of On Error Resume Next so you can see where the error is. — Mark Fitzgerald
Thank for the reply Mark. I got rid of what you suggested and now getting "Run-time error '1004' unable to get the Vlookup property of the WorksheetFunction Class" I had this error before and could not find anything on stackoverflow that helped except an online post in forum suggesting to trap the error which I did using On Error Resume Next. I am now where I was more than a week ago. — user2299013

A.S.H A.S.H · Accepted Answer · 2015-12-24T10:12:51

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

Vlookup doesn't seem to cooperate

1 Answers