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