find date on other sheet

[hans]

Can someone tell me how i can find a date on another sheet and if it is
found select the colorvalue two cells to the right.

Greetings Hans

 

[Frank Kabel]

Hi
try something like
=VLOOKUP(DATE(2004,1,1),'other_sheet'!A1:C100,3,0)

 

[hans]

oops sorry i need it in vba.

Greetings

Hi
try something like
=VLOOKUP(DATE(2004,1,1),'other_sheet'!A1:C100,3,0)

 

[Frank Kabel]

Hi
o.k. try
sub foo()
dim source_rng as range
dim lookup_value
dim ret_value
set source_rng = worksheets("other_sheet").range("A1:C100")
lookup_value=dateserial(2004,1,1)
ret_value=application.vlookup(lookup_value,source_rng,2,0)
if iserror(ret_value) then
msgbox "Date not found"
else
msgbox "Found value: " & ret_value
end if
end sub

 

[Frank Kabel]

Hi
sorry, you wanted the cell 2 columns to the right. So use the statement
ret_value=application.vlookup(lookup_value,source_rng,3,0)