Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem
I've got a sheet called data that contain referances and names and addresses and looks something like thi REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHON 1 10001A FRED 2 THE STREET THE TOWN CR0 012345467 2 10002A BOB 3 OTHER STREET TOWN2 RH1 012355874 3 20001C ADAM 4 OTHER STREET TOWN3 G34 0141574565 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 015455545 ETC... .. .. 1000...... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup I've tralled through pages of forums but seem to be getting myself more confused Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future thanks Ada |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
textbox2.text =
application.Vlookup(Textbox1.Text,Range("A1").Curr entRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
Tom,
Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
? application.Vlookup("ABC",Range("A1").CurrentRegio n,2,0)
Error 2042 returns an error value if no match is found. You can test for this condition Dim res as Variant res = application.Vlookup(Textbox1.Text,Range("A1").Curr entRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
Tom,
Thanks I have finally got it working, it seems that some of the references I was testing had spaces after them and some were formated incorrectly. Now that I've got it working, is there any way I can input just the last 4 digits of the reference into textbox1 rather than the whole reference. Thanks -----Original Message----- ? application.Vlookup("ABC",Range("A1").CurrentRegio n,2,0) Error 2042 returns an error value if no match is found. You can test for this condition Dim res as Variant res = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message news:13369732-831D-4F2E-8D97- ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
since your lookup items are stored as Text, then you can do something like:
=VLOOKUP("*234A",A1:A5,1,0) or in code: Dim res as Variant res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if This assumes the last four characters for a unique choice. -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks I have finally got it working, it seems that some of the references I was testing had spaces after them and some were formated incorrectly. Now that I've got it working, is there any way I can input just the last 4 digits of the reference into textbox1 rather than the whole reference. Thanks -----Original Message----- ? application.Vlookup("ABC",Range("A1").CurrentRegio n,2,0) Error 2042 returns an error value if no match is found. You can test for this condition Dim res as Variant res = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message news:13369732-831D-4F2E-8D97- ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
Tom, the code works great but is there anyway I can get it
to ignore some blank rows i've gone on the sheet (it seems to stop looking when it gets to a blank row) And also what changes to the code would I need to make to vloopup from coloum D instead of A. I've tried res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("D1").CurrentRegion,2,0) But this is obviously wrong because it doesnt work. Thanks -----Original Message----- since your lookup items are stored as Text, then you can do something like: =VLOOKUP("*234A",A1:A5,1,0) or in code: Dim res as Variant res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if This assumes the last four characters for a unique choice. -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks I have finally got it working, it seems that some of the references I was testing had spaces after them and some were formated incorrectly. Now that I've got it working, is there any way I can input just the last 4 digits of the reference into textbox1 rather than the whole reference. Thanks -----Original Message----- ? application.Vlookup("ABC",Range ("A1").CurrentRegion,2,0) Error 2042 returns an error value if no match is found. You can test for this condition Dim res as Variant res = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message news:13369732-831D-4F2E-8D97- ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
Dim rngD as Range
set rngD = Range(cells(1,"D"),Cells(rows.count,"D").End(xlup) ) res = Application.Vlookup("*" & trim(Textbox1.Text), _ rngD.Resize(,10),2,0) Change the 10 in ,10 to reflect the number of columns of data you have. -- Regards, Tom Ogilvy "Adam" wrote in message ... Tom, the code works great but is there anyway I can get it to ignore some blank rows i've gone on the sheet (it seems to stop looking when it gets to a blank row) And also what changes to the code would I need to make to vloopup from coloum D instead of A. I've tried res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("D1").CurrentRegion,2,0) But this is obviously wrong because it doesnt work. Thanks -----Original Message----- since your lookup items are stored as Text, then you can do something like: =VLOOKUP("*234A",A1:A5,1,0) or in code: Dim res as Variant res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if This assumes the last four characters for a unique choice. -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks I have finally got it working, it seems that some of the references I was testing had spaces after them and some were formated incorrectly. Now that I've got it working, is there any way I can input just the last 4 digits of the reference into textbox1 rather than the whole reference. Thanks -----Original Message----- ? application.Vlookup("ABC",Range ("A1").CurrentRegion,2,0) Error 2042 returns an error value if no match is found. You can test for this condition Dim res as Variant res = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message news:13369732-831D-4F2E-8D97- ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . . . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup and text
Thanks Again Tom,
one last question if I may... Some of the textboxes will need some vba code to look to the left of the D column. From what I've been reading I need to use the match command. Do you know any code I could use to accuplish this? Thanks Adam "Tom Ogilvy" wrote in message ... Dim rngD as Range set rngD = Range(cells(1,"D"),Cells(rows.count,"D").End(xlup) ) res = Application.Vlookup("*" & trim(Textbox1.Text), _ rngD.Resize(,10),2,0) Change the 10 in ,10 to reflect the number of columns of data you have. -- Regards, Tom Ogilvy "Adam" wrote in message ... Tom, the code works great but is there anyway I can get it to ignore some blank rows i've gone on the sheet (it seems to stop looking when it gets to a blank row) And also what changes to the code would I need to make to vloopup from coloum D instead of A. I've tried res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("D1").CurrentRegion,2,0) But this is obviously wrong because it doesnt work. Thanks -----Original Message----- since your lookup items are stored as Text, then you can do something like: =VLOOKUP("*234A",A1:A5,1,0) or in code: Dim res as Variant res = application.Vlookup("*" & trim(Textbox1.Text), _ Range("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if This assumes the last four characters for a unique choice. -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks I have finally got it working, it seems that some of the references I was testing had spaces after them and some were formated incorrectly. Now that I've got it working, is there any way I can input just the last 4 digits of the reference into textbox1 rather than the whole reference. Thanks -----Original Message----- ? application.Vlookup("ABC",Range ("A1").CurrentRegion,2,0) Error 2042 returns an error value if no match is found. You can test for this condition Dim res as Variant res = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) if not iserror(res) then Textbox2.Text = res else Textbox2.Text = "Not found" End if -- Regards, Tom Ogilvy Adam wrote in message ... Tom, Thanks for the help but all i'm getting is runtime error 13 - type mismatch Any Ideas what I'm doing wrong? -----Original Message----- textbox2.text = application.Vlookup(Textbox1.Text,Range ("A1").CurrentRegion,2,0) -- Regards, Tom Ogilvy Adam wrote in message news:13369732-831D-4F2E-8D97- ... Ok, i'm a bit of a newbie and teaching myself as I go along but here is my problem. I've got a sheet called data that contain referances and names and addresses and looks something like this REFERENCE NAME ADDRESS1 ADDRESS2 POSTCODE TELEPHONE 1 10001A FRED 2 THE STREET THE TOWN CR0 0123454677 2 10002A BOB 3 OTHER STREET TOWN2 RH1 0123558745 3 20001C ADAM 4 OTHER STREET TOWN3 G34 01415745655 4 10003A SUSAN 5 OTHER PLACE TOWN4 F54 0154555456 ETC.... ... ... 1000....... I want to enter the reference (which is always be both numbers and text) into textbox1 and pull the other cells into textbox2, 3 & 4 etc using vlookup. I've tralled through pages of forums but seem to be getting myself more confused. Can someone provide me with the code using vlookup and expain it so that I can use it correctly in future. thanks. Adam . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Text | Excel Discussion (Misc queries) | |||
look for text not using vlookup and sum if | Excel Discussion (Misc queries) | |||
Using VLOOKUP and text | Excel Discussion (Misc queries) | |||
Vlookup using text | Excel Worksheet Functions | |||
Vlookup a value and text | Excel Discussion (Misc queries) |