vlookup
Can you help me, why this script doesn't work?
I ahve two sheets with names in different order. I want to get value from col E for a certain name (names are in col C) in sheet "hodiny" and paste it next to the same name (names are in col D) in sheet "list" in col I. I tried to use the script I post here, but it causes an error message Thank you Milos Sub hodiny_sep() Dim res As Long Dim i As Long i = 3 Do While Sheets("List").Range("D" & i).Value < "" res = Application.VLookup(Sheets("List").Range("D" & i), Sheets("hodiny").Range("C2:C200"), 7, False) Sheets("List").Range("I" & i).Value = Sheets("hodiny").Range("E" & res).Value x = x + 1 Loop End Sub |
vlookup
Hi
Do you mean i = i+1? Also, what is the error message and which line of code is highlighted? regards Paul milos wrote: Can you help me, why this script doesn't work? I ahve two sheets with names in different order. I want to get value from col E for a certain name (names are in col C) in sheet "hodiny" and paste it next to the same name (names are in col D) in sheet "list" in col I. I tried to use the script I post here, but it causes an error message Thank you Milos Sub hodiny_sep() Dim res As Long Dim i As Long i = 3 Do While Sheets("List").Range("D" & i).Value < "" res = Application.VLookup(Sheets("List").Range("D" & i), Sheets("hodiny").Range("C2:C200"), 7, False) Sheets("List").Range("I" & i).Value = Sheets("hodiny").Range("E" & res).Value x = x + 1 Loop End Sub |
vlookup
try
Application.WorksheetFunction.VLookup(Sheets("List ").Range("D" & i), Sheets("hodiny").Range("C2:C200"), 7, False) "milos" wrote: Can you help me, why this script doesn't work? I ahve two sheets with names in different order. I want to get value from col E for a certain name (names are in col C) in sheet "hodiny" and paste it next to the same name (names are in col D) in sheet "list" in col I. I tried to use the script I post here, but it causes an error message Thank you Milos Sub hodiny_sep() Dim res As Long Dim i As Long i = 3 Do While Sheets("List").Range("D" & i).Value < "" res = Application.VLookup(Sheets("List").Range("D" & i), Sheets("hodiny").Range("C2:C200"), 7, False) Sheets("List").Range("I" & i).Value = Sheets("hodiny").Range("E" & res).Value x = x + 1 Loop End Sub |
vlookup
Hi
It could also be that the value you are looking for is not in your table, which will generate an error. Try putting you vlookup between the two lines on error resume next 'your vlookup on error goto 0 to ignore the error. regards Paul milos wrote: Can you help me, why this script doesn't work? I ahve two sheets with names in different order. I want to get value from col E for a certain name (names are in col C) in sheet "hodiny" and paste it next to the same name (names are in col D) in sheet "list" in col I. I tried to use the script I post here, but it causes an error message Thank you Milos Sub hodiny_sep() Dim res As Long Dim i As Long i = 3 Do While Sheets("List").Range("D" & i).Value < "" res = Application.VLookup(Sheets("List").Range("D" & i), Sheets("hodiny").Range("C2:C200"), 7, False) Sheets("List").Range("I" & i).Value = Sheets("hodiny").Range("E" & res).Value x = x + 1 Loop End Sub |
vlookup
One problem is that you're trying to return column 7 from a one column range.
And you write that you want to return the value from column E (but matching on column C). Maybe you should expand C2:C200 to C2:E200 and return the 3rd column? Sub hodiny_sep() Dim res As variant 'changed, since it could return an error Dim i As Long i = 3 Do While Sheets("List").Range("D" & i).Value < "" res = Application.VLookup(Sheets("List").Range("D" & i).value, _ Sheets("hodiny").Range("C2:E200"), 3, False) if iserror(res) then res = "missing" end if sheets("List").Range("I" & i).Value = res i = i + 1 Loop End Sub Compiled, but untested. milos wrote: Can you help me, why this script doesn't work? I ahve two sheets with names in different order. I want to get value from col E for a certain name (names are in col C) in sheet "hodiny" and paste it next to the same name (names are in col D) in sheet "list" in col I. I tried to use the script I post here, but it causes an error message Thank you Milos Sub hodiny_sep() Dim res As Long Dim i As Long i = 3 Do While Sheets("List").Range("D" & i).Value < "" res = Application.VLookup(Sheets("List").Range("D" & i), Sheets("hodiny").Range("C2:C200"), 7, False) Sheets("List").Range("I" & i).Value = Sheets("hodiny").Range("E" & res).Value x = x + 1 Loop End Sub -- Dave Peterson |
All times are GMT +1. The time now is 05:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com