CODEWITHSUNDEEP
vbaexcelvlookupexcel-2013
RobExcel
RobExcel

Reputation: 191

Using VlookUp in VBA to search from bottom to top

I'm using a piece of VBA in a bigger macro to backup some of the comments made. When returning these comments I'm using Vlookup in VBA. The problem is, Vlookup searches from top to bottom. I want to turn this around and make it search from bottom to top. How would i go and achieve this? I've run out of Google-options. Trying to learn from this, so please explain your solution, if possible.

To be exact, I'm using the code added below:

Dim Dept_Row As Long
Dim Dept_Clm As Long
Table1 = Sheets("16-Compliancy-Rebuild").Range("D85:D750")
Table2 = Sheets("OpmerkingBackup").Range("B2:F750")
Dept_Row = Sheets("16-Compliancy-Rebuild").Range("H85").Row
Dept_Clm = Sheets("16-Compliancy-Rebuild").Range("H85").Column
For Each cl In Table1
  Sheets("16-Compliancy-Rebuild").Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(cl, Table2, 5, False)
  Dept_Row = Dept_Row + 1
Next cl

Upvotes: 0

Views: 2301

Answers (1)

Plagon
Plagon

Reputation: 3139

So instead of using a VlookUp, you can use .Find instead. With this function you can clarify the Searchdirection, xlPrevious will search from bottom to top. The only difference is in the returnvalue, it gives back the cell with the match. The row of the Match (rng.Row) can now be used to get the dataset from the right column. 

Dim sht As Worksheet, sht As Worksheet
Set sht = Sheets("16-Compliancy-Rebuild")
Set sht2 = Sheets("OpmerkingBackup")
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim rng As Range
Table1 = sht.Range("D85:D750")
Table2 = sht2.Range("B2:B750")
Dept_Row = sht.Range("H85").Row
Dept_Clm = sht.Range("H85").Column
For Each cl In Table1
    Set rng = Table2.Find(cl, SearchDirection:=xlPrevious, LookAt:=xlWhole)
    If Not rng Is Nothing Then
        sht.Cells(Dept_Row, Dept_Clm) = sht2.Cells(rng.Row, 6).Value
    End If
  Dept_Row = Dept_Row + 1
Next cl

Other than that i also use variables for the Worksheets to make it easier to change and lookover.

Upvotes: 3

Related Questions