mimic vlookup in VBA

[Jeff98]

two sheets s1has column s1-c1 s1-c2
s2 has columm s2-c1
What is want to do is that for each element in s1-c1 if it is in s2-c1 return true, otherwise return false. teh results are written to s1-c2.

How can I do in in VBA?

THX

 

[SkipVought]

Hi,

You can use the MATCH spreadsheet function to return a value or not and code T or F accordingly.

  dim n, r as range
  for each r in sheet1.range(sheet1.[c1], sheet1.[c1].end(xldown))
    n = application.match(r.value, sheet2.range(sheet2.[c1], sheet2.[c1].end(xldown)),0)
    if iserror(n) then
      r.offset(0,1).value = false
    else
      r.offset(0,1).value = true
    end if
    
  next

Skip,

_[red][/red]