Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? | Excel Programming | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel |