VBA Vlookup

[straws]

Please help!

I need to replace "Ticker Symbols" with the full name. I have the
tickers in column F sheet 1, and I have the table in sheet 2 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 F sheet 1 with
the full name in sheet 2 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

 

[Mike H]

Hi,

This would be easier to answer if we could see the worksheet Vlookup your
using.

Mike

 

[straws]

Hi,

This would be easier to answer if we could see the worksheet Vlookup your
using.

Mike

=IF(ISNA(VLOOKUP(D2,OrderLegend!A:B,2,0)),D2,(VLOOKUP(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!!

 

[straws]

Test the VLookup for an error and ignore it if there is an error. here isan
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

I need to replace "Ticker Symbols" with the full name. I have the
=IF(ISNA(VLOOKUP(D2,OrderLegend!A:B,2,0)),D2,(VLOOKUP(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.

 

[Mike H]

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]

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 inon
the right and run it. If the lookup fails it will return the value of RAWD2

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

Thanks - we are very close. I think I need to add a loop to this, as
I have about 250 rows in Column D Raw that need replacing. Also - I
don't need the message box - I just need to replace the value in
column D Raw - For example - IBM > Intentional Business Machine, GOOG

Google, MSFT > Microsoft ect.

Again - thanks - you have done more in 30mins what I have done in 7
hrs!

 

[Mike H]

Maybe this

Sub marine()
Dim MyRange As Range, MyRange1 As Range
Set MyRange = ThisWorkbook.Sheets("Orderlegend").Range("A:B")
lastrow = Sheets("Raw").Cells(Rows.Count, "D").End(xlUp).Row
Set MyRange1 = Sheets("RAW").Range("D2" & lastrow)
For Each c In MyRange1
myvalue = c.Value
If IsError(Application.VLookup(myvalue, MyRange, 2, False)) Then
'do nothing
Else
c.Value = Application.VLookup(myvalue, MyRange, 2, False)
End If
Next
End Sub

Mike

 

[Don Guillett]

You may like this better.

Sub replacetickers()
For Each mc In Sheets("Sheet1").Range("f1:f6")'adjust to suit
With Worksheets("sheet2").Range("a1:a500")
Set c = .Find(mc, LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
mc.Value = c.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next mc
End Sub

 

[straws]

Maybe this

Sub marine()
Dim MyRange As Range, MyRange1 As Range
Set MyRange = ThisWorkbook.Sheets("Orderlegend").Range("A:B")
lastrow = Sheets("Raw").Cells(Rows.Count, "D").End(xlUp).Row
Set MyRange1 = Sheets("RAW").Range("D2" & lastrow)
For Each c In MyRange1
    myvalue = c.Value
    If IsError(Application.VLookup(myvalue, MyRange, 2, False)) Then
     'do nothing
    Else
        c.Value = Application.VLookup(myvalue, MyRange, 2, False)
    End If
Next
End Sub

Mike

Mike - If your name was Michelle I would send you flowers!! Thank you
very much.

 

[Don Guillett]

Did you try using mine with vba instead of worksheet.functions?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Maybe this

Sub marine()
Dim MyRange As Range, MyRange1 As Range
Set MyRange = ThisWorkbook.Sheets("Orderlegend").Range("A:B")
lastrow = Sheets("Raw").Cells(Rows.Count, "D").End(xlUp).Row
Set MyRange1 = Sheets("RAW").Range("D2" & lastrow)
For Each c In MyRange1
myvalue = c.Value
If IsError(Application.VLookup(myvalue, MyRange, 2, False)) Then
'do nothing
Else
c.Value = Application.VLookup(myvalue, MyRange, 2, False)
End If
Next
End Sub

Mike

Mike - If your name was Michelle I would send you flowers!! Thank you
very much.

 

[Mike H]

Glad I could help and I'll pass on the flowers.

Just a point. I persisted with application.vlookup because that's what you
started with but it's not necessarily the best or most effecient. Check your
other response from Don G.

Mike

 

[straws]

You may like this better.

Sub replacetickers()
For Each mc In Sheets("Sheet1").Range("f1:f6")'adjust to suit
With Worksheets("sheet2").Range("a1:a500")
    Set c = .Find(mc, LookIn:=xlValues, LookAt:=xlWhole, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
           mc.Value = c.Offset(, 1)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
Next mc
End Sub

No - Mike's worked fine, but I will also try yours. What is the
advantage of your? Thanks for you help.