VBA VLookup in Loop

Question

I´m trying to do a VLOOKUP of a column data set at a Sheet called "SyS" in G column. and I'd like to Vlookup relevant data using columns in another sheet called "CONF_mapping", located in the same Workbook. I need to find my data located at the range ("A1:E65000") (It's at column A, but I need to retrieve data from other columns with my vlookup to SyS). I'm not getting good results with my code, and I beg your pardon, it´s my first question in the forum.

Worksheets("SyS").Select
Dim wsThis As Worksheet
Dim aCell As Range

Sheets("CONF_mapping").Columns(2).Copy Destination:=Sheets("SyS").Columns(8)
Set wsThis = Sheets("SyS")
    With wsThis
        For Each aCell In .Range("A1:E65000")
            '.Cells(aCell.Row, 8) = "Not Found"
            On Error Resume Next
            .Cells(aCell.Row, 8) = Application.WorksheetFunction.VLookup( _
                            aCell.value, wsThat.Range("G2:G65000"), 2, False)
            On Error GoTo 0
         Next aCell
     End With
Worksheets("SyS").Select

I have find this code but I was not able to make it works for me. I would appreciate any help.

Its just copy the column 2 of CONF_mapping in column 8 of SyS instead of doing the vlookup.. Im not able to do it work... — Guillermo

Khizhny Andrey Khizhny Andrey · Accepted Answer · 2018-01-23T13:11:20

You have mistake here:

VLookup(aCell.value, wsThat.Range("G2:G65000"), 2, False)

Range "G2:G65000" Have just 1 column G, but you try to get column#2 which does not exists.

UPD: I guess you need something like this:

Const COLUMN_TO_MATCH_IN_SYS = 8 Const COLUMN_TO_MATCH_IN_CONF = 1 Sub test() Dim wsSys As Worksheet Dim wsConf As Worksheet Set wsSys = Sheets("SyS") Set wsConf = Sheets("CONF_mapping") Dim RowSys As Range Dim RowConf As Range For Each RowSys In wsSys.UsedRange.Rows For Each RowConf In wsConf.UsedRange.Rows If RowSys.Cells(1, COLUMN_TO_MATCH_IN_SYS) = _ RowConf.Cells(1, COLUMN_TO_MATCH_IN_CONF) Then ' Copy row values which is needed RowSys.Cells(1, 6) = RowConf.Cells(1, 1) ' From column A(conf) to G(sys) RowSys.Cells(1, 7) = RowConf.Cells(1, 2) ' From column B(conf) to H (sys) End If Next aCell Next End Sub

With this solution you don't need to search the Range for each cell (just for each row), so it will work 5 times faster.

VBA VLookup in Loop

1 Answers