Frank never tested the code he posted - there are a couple of errors.

Try:

Public Sub foo()
Dim look_rng As Range
Dim source_rng As Range
Dim target_rng As Range
Dim ret_Value As Variant
With ActiveSheet
Set look_rng = .Range("A1")
Set target_rng = .Range("B1")
Set source_rng = .Range("C1:D100")
End With
ret_Value = Application.VLookup(look_rng, source_rng, 2, 0)
If IsError(ret_Value) Then ret_Value = ""
target_rng = ret_Value
End Sub


In article ,
hey wrote:

Below what you gave me last week doesnt work. It doesnt like the last end
if?

Thanks


sub foo()
dim look_rng as range
dim source_rng as range
dim target_rng as range
dim ret_value
with activesheet
set look_rng = .range("A1")
set target_rng = .rnage("B1")
set source_rng = .range("C1:D100")
end with
ret_value=application.vlookup(look_rng,source_rng, 2,0)
if iserror(ret_value) then
ret_value=""
end if
target_rng=ret_value
end if