Try the below which will auto adjust depending on the number or rows in
Column J

Sub MyMacro()
Dim lngRow As Long
lngRow = Cells(Rows.Count, "J").End(xlUp).Row
Range("K4:K" & lngRow).Formula = _
"=IF(ISERROR(VLOOKUP($J4,Sheet2!$A$1:$B$56,2,FALSE ))," & _
""""",VLOOKUP($J4,Sheet2!$A$1:$B$56,2,FALSE))"
Range("K4:K" & lngRow).Value = Range("K4:K" & lngRow).Value
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Wanna Learn" wrote:

Hello

This is my macro
Range("K4").Select
ActiveCell.FormulaR1C1 = _

"=IF(ISERROR(VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALS E)),"""",VLOOKUP(RC10,Sheet2!R1C1:R56C2,2,FALSE))"
Range("K4").Select
Selection.AutoFill Destination:=Range("K4:K7282")
Range("K4:K7282").Select
Columns("K:K").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K3").Select
End Sub
I get the results I want but ...This is a macro that I have to run everyday.
So the auto fill range will be different every day . How do I correct the
macro? thanks