lookup between different worksheet.

[inurface]

Hi everyone,

First post here, I am trying to write a VBA that can lookup the value in sheet"ac mapping" to sheet "status run", but some error occur, can anyone help check if anything goes wrong?


Sub lookupac()

Dim lastrowB As Long




lastrowB = Cells(Sheets("status run").Rows.Count, "N").End(xlUp).Row


Set acmapping = Sheets("ac mapping").Range("A:B")




For i = 2 To lastrowB


Cells(i, 15) = Worksheet.Application.WorksheetFunction.VLookup(Cells(i, 2), acmapping, 2, False)



Next i

End Sub

[p45cal]

Take your choice:
Sub lookupac()
Dim lastrowB As Long, acmapping As Range, i As Long

Set acmapping = Sheets("ac mapping").Range("A:B")
With Sheets("status run")
lastrowB = .Cells(.Rows.Count, "N").End(xlUp).Row
For i = 2 To lastrowB
.Cells(i, 15) = Application.VLookup(.Cells(i, 2), acmapping, 2, False)
Next i
End With
End Sub

Sub lookupac2()
Dim acmapping As Range, i As Long

Set acmapping = Sheets("ac mapping").Range("A:B")
With Sheets("status run")
For i = 2 To .Cells(.Rows.Count, "N").End(xlUp).Row
.Cells(i, 15) = Application.VLookup(.Cells(i, 2), acmapping, 2, False)
Next i
End With
End Sub

Sub lookupac3()
With Sheets("status run")
With .Range("O2:O" & .Cells(.Rows.Count, "N").End(xlUp).Row)
.FormulaR1C1 = "=VLOOKUP(RC2,'ac mapping'!C1:C2,2,0)"
.Value = .Value
End With
End With
End Sub

[inurface]

Wow that's quick, thx~~~