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

Hi,

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

Mike

"straws" wrote:

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

On Sep 23, 7:08*am, Mike H wrote:
Hi,

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

Mike

"straws" wrote:
Please help!

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!!

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
...
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

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.

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.

On Sep 23, 8:33*am, Mike H wrote:
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.

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!

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:D" & 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

"straws" wrote:

On Sep 23, 8:33 am, Mike H wrote:
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.

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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"straws" wrote in message
...
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

On Sep 23, 9:26*am, Mike H wrote:
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:D" & 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

"straws" wrote:
On Sep 23, 8:33 am, Mike H wrote:
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.

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 - If your name was Michelle I would send you flowers!! Thank you
very much.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"straws" wrote in message
...
On Sep 23, 9:26 am, Mike H wrote:
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:D" & 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

"straws" wrote:
On Sep 23, 8:33 am, Mike H wrote:
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.

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 - If your name was Michelle I would send you flowers!! Thank you
very much.

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" wrote:

On Sep 23, 9:26 am, Mike H wrote:
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:D" & 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

"straws" wrote:
On Sep 23, 8:33 am, Mike H wrote:
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.

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 - If your name was Michelle I would send you flowers!! Thank you
very much.

On Sep 23, 10:04*am, "Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"straws" wrote in message

...

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

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