FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
This code works fine to replace the value in column 2 of Sheet1 with a Vlookup result from Sheet2 column 2.
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?
Code:
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?
Code:
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