how to do vlookup to a dynamic table and autofill without using loop

Question

I have 2 tables in different worksheets and i have to do vlookup to get the data from both these dynamic tables. I have done this using a for loop but sue to large data, excel is crashing. Is there any other way to do this?

The columns in the lookup array will be constant. But the rows will keep on changing.

Sheets("HRG").Activate

lastrow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
lastcolumn = ActiveSheet.UsedRange.Column + 
ActiveSheet.UsedRange.Columns.Count - 1
Set VLRange = ActiveSheet.Range(Cells(2, 1), Cells(lastrow, lastcolumn))

Sheets("HRA").Activate

With ActiveSheet
    lastrowHRA = .Range("A" & .Rows.Count).End(xlUp).Row
End With

For i = 2 To lastrowHRA
    ActiveSheet.Cells(i, lastColHRA + 1) = Application.VLookup(ActiveSheet.Cells(i, 1), VLRange, 11, False)
    ActiveSheet.Cells(i, lastColHRA + 2) = Application.VLookup(ActiveSheet.Cells(i, 1), VLRange, 53, False)
Next i

Note: Don't use `.Activate` and `ActiveSheet`. Instead use `With Worksheets("HRA")` and start use `.Cells` without `ActiveSheet`. You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 22 '19 at 11:50

score 0 · Answer 1 · answered May 22 '19 at 12:54

For all intents and purposes, VLOOKUP simply looks up a value in one column and returns the value in another column for that row.

Option Explicit

Sub HRG2HRa()

    Dim i As Long, j As Long, m As Variant
    Dim hrgA As Variant, hrgK As Variant, hrgBA As Variant
    Dim hraA As Variant, hra2C As Variant

    With Worksheets("HRG")

        i = .Cells(.Rows.Count, "A").End(xlUp).Row

        hrgA = .Range(.Cells(2, "A"), .Cells(i, "A")).Value2
        hrgK = .Range(.Cells(2, "K"), .Cells(i, "K")).Value2
        hrgBA = .Range(.Cells(2, "BA"), .Cells(i, "BA")).Value2

    End With

    With Worksheets("HRA")

        i = .Cells(.Rows.Count, "A").End(xlUp).Row
        j = .Cells(1, .Columns.Count).End(xlToLeft).Column

        hraA = .Range(.Cells(2, "A"), .Cells(i, "A")).Value2
        ReDim hra2C(LBound(hraA, 1) To UBound(hraA, 1), 1 To 2)

        For i = LBound(hraA, 1) To UBound(hraA, 1)

            m = Application.Match(hraA(i, 1), hrgA, 0)

            If Not IsError(m) Then

                hra2C(i, 1) = hrgK(m, 1)
                hra2C(i, 2) = hrgBA(m, 1)

            End If

        Next i

        .Cells(2, j + 1).Resize(UBound(hra2C, 1), UBound(hra2C, 2)) = hra2C

    End With

End Sub

I have tried this. But the same issue. Excel is going to 'not responding' mode. The volume of data is huge. — Uthara, May 22 '19 at 14:24

how to do vlookup to a dynamic table and autofill without using loop

1 Answers1