VBA: Vlookup to replace column of data from table

[FryGirl]

This code works fine to replace the value in column 2 of Sheet1 with a Vlookup result from Sheet2 column 2.

Sub DoVlookup()
    Dim ws1     As Worksheet: Set ws1 = Sheets("Sheet1")
    Dim ws2     As Worksheet: Set ws2 = Sheets("Sheet2")
    Dim Lr      As Long: Lr = ws1.Range("B" & Rows.Count).End(xlUp).Row
    Dim rng     As Range: Set rng = ws2.Range("A2:B7")
    Dim i       As Long
    For i = 2 To Lr
        ws1.Range("B" & i).Value = WorksheetFunction.VLookup(ws1.Range("B" & i).Value, rng, 2, 0)
    Next i
End Sub

I ran across some code which uses an Array and Replace to do the same thing, but it was written as a toggle feature which I don't need. Every subsequent time you run the code it replaces the values from Sheet2, either column 1 or column 2 with the value in Sheet1 column 2.

I don't need to swap the values, I would just like to get the value from Sheet2 column 2 to replace the value in Sheet1 column 2.

Could somebody show me how the .Replace line should be modified to work with the array construct?

Sub test()
    Dim a, i As Long
    Static flg As Boolean
    flg = Not flg
    a = Sheets("Sheets2").Cells(1).CurrentRegion.Resize(, 2).Value
    With Sheets("Sheets1").Columns(2)
        For i = 2 To UBound(a, 1)
            .Replace a(i, IIf(flg, 2, 1)), a(i, IIf(flg, 1, 2)), 1
        Next
        .AutoFit
    End With
End Sub

 

[Fluff]

Maybe

Sub test()
    Dim a, i As Long
    a = Sheets("Sheets2").Cells(1).CurrentRegion.Resize(, 2).Value
    With Sheets("Sheets1").Columns(2)
        For i = 2 To UBound(a, 1)
            .Replace a(i, 1), a(i, 2), xlWhole, , False, , False, False
        Next
        .AutoFit
    End With
End Sub

 

[FryGirl]

Thank you Fluff. This does the trick.

 

[Fluff]

You're welcome & thanks for the feedback