Vlookup does not update return value but copy the same result value of the first Cell

Occasional Visitor

Sub Clean()
'
' Clean Macro
'

Dim FirstCellAddress As String
Dim LastCellAddress As String

FirstCellAddress = ActiveCell.Address

LastCellAddress = ActiveCell.SpecialCells(xlLastCell).Offset(0, 1).Address
ActiveCell.SpecialCells(xlLastCell).Offset(0, 1).Activate

Range(FirstCellAddress & ":" & LastCellAddress, ActiveCell).Select

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1], 'ACS2000 Reeference tables.xlsx'!Table_a2000cdc_hwunit_modes_1[[hwunit_mode_num]:[hwunit_mode_name]],2,FALSE)"

ActiveCell.Select
Selection.Copy
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
ActiveSheet.Paste


End Sub

0 Replies