|
VBA Vlookup
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 |
VBA Vlookup
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 |
VBA Vlookup
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!! |
VBA Vlookup
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 |
VBA Vlookup
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. |
VBA Vlookup
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. |
VBA Vlookup
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! |
VBA Vlookup
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! |
VBA Vlookup
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. |
VBA Vlookup
|
VBA Vlookup
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. |
VBA Vlookup
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. |
| All times are GMT +1. The time now is 08:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com