Hi,

This will work as worksheet or workbook code but I recommend workbok so:-
Alt+F11 to open Vb editor. double click 'This workbook' and paste this in on
the right and run it. If the lookup fails it will return the value of RAW D2

Sub marine()
Dim MyRange As Range
myvalue = Sheets("Raw").Range("D2").Value
Set MyRange = ThisWorkbook.Sheets("Orderlegend").Range("A:B")
If IsError(Application.VLookup(myvalue, MyRange, 2, False)) Then
res = myvalue
Else
res = Application.VLookup(myvalue, MyRange, 2, False)
End If
MsgBox res
End Sub

Mike
"straws" wrote:

On Sep 23, 7:51 am, "Mike Fogleman" wrote:
Test the VLookup for an error and ignore it if there is an error. here is an
excerpt from one of my codes:

If IsError(Application.VLookup(Mystr, rng2, 4, 0)) = True Then
' do nothing
Else
c.Value = Application.VLookup(Mystr, rng2, 4, 0)
End If

Mike F"straws" wrote in message

...

I need to replace "Ticker Symbols" with the full name. I have the
tickers in column D Raw!, and I have the table in OrderLegend! with the
tickers in column A and the full name in column B. I have been using
vlookup formula, but now I need it in VBA. It should be simple, but I
am struggling. I just need it to replace the ticker in D Raw! with
the full name in OrderLegend! B. The kicker is if the ticker in not in the
column A of sheet 2, then I need it to ignore it and just leave the
ticker as is. This can't be difficult - but I am having a hard time.
Any code would be greatly appreciated! - Thanks

=IF(ISNA(VLOOKUP(D2,OrderLegend!A:B,2,0)),D2,(VLOO KUP(D2,OrderLegend!
A:B,2,0)))

D2 "Raw!" sheet is the ticker (that needs to be replaced with the full
name), OrderLegend! A:B is the ticker (A) and full name(B)

Thanks for your response - however, I am not even that far yet. I am
having trouble getting off the ground.