excel - Iteration through column to find a particular value -

i trying go through column of empty cells in excel spreadsheet in order find row in word "yes" found. afterwards, upon finding word in particular row, instance in cell d23, want go on 1 column cell e23 , paste value in cell cell b100. here have far, doesn't seem functioning correctly:

     sub test3()       dim x string       x = "yes"       ' dim found boolean       ' select first line of data.       range("d4").select       ' set search variable value.       ' set boolean variable "found" false.       found = false       ' set loop stop @ empty cell.       until activecell.value = x          ' check active cell search value.          if activecell.value = x             range("b100").value = activecell.offset(0, 1).value             found = true             exit          end if          ' step down 1 row present location.          activecell.offset(1, 0).select       loop    ' check found.            if found = true          msgbox "value found in cell " & activecell.address       else          msgbox "value not found"       end if    end sub 

thanks!

as @tigeravatar mentioned in comment, you'd better off using excel's native functions, but, if want via vba, can more , efficiently using find function returns range if found or else 'nothing' if not.

using that, can test see got back. try this:

sub test3() dim x string dim rng range      x = "yes"      set rng = range("d4:d10000").find(x)      if not rng nothing         range("b100").value = rng.offset(0, 1).value         msgbox "value found in cell " & rng.address     else         msgbox "value not found"     end if end sub 

hope trick.