vlookup

[Guest]

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

 

[paul.robinson]

Hi
Do you mean i = i+1?
Also, what is the error message and which line of code is highlighted?
regards
Paul

 

[Guest]

try
Application.WorksheetFunction.VLookup(Sheets("List").Range("D" & i),
Sheets("hodiny").Range("C2:C200"), 7, False)

 

[paul.robinson]

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

 

[Dave Peterson]

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.