vba - For Each loop not going through all the data -

i have simple macro goes through series of sheets, gathering names based on data inputted, puts in nicely formatted word document. have of figured out, 1 bug annoying me. has code gets cell phone number based on name. here function:

function findcell(nameperson string) string     dim splitname variant     dim lastname string     dim firstname string      splitname = split(nameperson, " ")     lastname = splitname(ubound(splitname))     redim preserve splitname(ubound(splitname) - 1)      firstname = join(splitname)      each b in worksheets("it").columns(1).cells         if b.value = lastname             if sheets("it").cells(b.row, 2).value = firstname findcell = sheets("it").cells(b.row, 4).value         end if     next end function 

the cellphone numbers on own sheet called "it". first column has last name, second column has first name, , forth column has cell phone number. people have multiple parts first name, , that's why see of weird splitting, redim-ing , joining together. part works fine.

the problem arises when have multiple people same last name. function find right last name, going through first if statement. compare first name. if matches, return value of cell phone number should. after that, loop stops, if first name doesn't match up. if happens same last name, first name doesn't check up, returns nothing.

i've tried putting return call outside of loop together, , still doesn't make difference.

since you're not using database, primary key column might difficult. current set try this. it

• doesn't through every single cell in column
• uses option explicit
• will return first find , exit
• will indifferent upper/lower case , leading/trailing white space.

.

option explicit  function findcell(nameperson string) string     dim splitname variant     dim lastname string     dim firstname string      splitname = split(nameperson, " ")     lastname = splitname(ubound(splitname))     redim preserve splitname(ubound(splitname) - 1)     firstname = join(splitname)      dim ws worksheet, lastrow long, r long     set ws = worksheets("it")     lastrow = ws.cells(1, 1).end(xldown).row 'or whatever cell      r = 1 lastrow         if ucase(trim(ws.cells(r, 1))) = ucase(trim(lastname)) _             , ucase(trim(ws.cells(r, 2))) = ucase(trim(firstname))              findcell = ws.cells(r, 4)             exit         end if      next r  end function