If I'm right here is macro that will fill column C of Sheet 1 with the formula suggested by protonLeah.
Sub PlatVlookUp()
Dim CalcState
Dim LastRow1 As Long
Dim LastRow2 As Long
With Application
.ScreenUpdating = False
CalcState = .Calculation
.Calculation = xlCalculationManual
End With
Worksheets("Sheet1").Activate
LastRow1 = Worksheets("Sheet2").Range(Worksheets("Sheet2").Cells(Rows.Count, _
1), Worksheets("Sheet2").Cells(Rows.Count, 1)).End(xlUp).Row
LastRow2 = Range(Cells(Rows.Count, 1), Cells(Rows.Count, 1)).End(xlUp).Row
With Range("C2")
.FormulaR1C1 = _
"=IF(OR(RC[-1]="""",RC[-1]=0),"""",VLOOKUP(RC[-1],Sheet2!R2C1:R" & LastRow1 _
& "C2,2,FALSE))"
.AutoFill Destination:=Range("C2:C5" & LastRow2)
ActiveSheet.Calculate
.EntireColumn.Copy
.EntireColumn.PasteSpecial Paste:=xlPasteValues
End With
With Application
.Calculation = CalcState
.ScreenUpdating = True
End With
End Sub
It will find the last row in column A on sheet2 and use it in the formula. It will copy the formula down to the last row in column A on Sheet1.
Please, note that the sheet's names are hard-coded.
Buran
Bookmarks