excel - vba: Find function with .rows returning an unexpected result -
i trying use find function make wider code applicable multiple instances of same basic table. table headers left of table code
preschoolstart = (sheets("rotas").columns(3).find(what:="school starters", lookat:=xlwhole, searchdirection:=xlnext, matchcase:=false).row) + 1
which finds header "school starters" , sends reference cell below contain first of several names in category "preschool".
this works in instance above , in 2 other instances same format when used below case returns row value far below expected.
staffstart = (columns(3).find(what:="staff", lookat:=xlwhole, searchdirection:=xlnext, matchcase:=false).row) + 1
my original theory why there other headers staff in name confusing things have been fixed changes made on advice of @jeeped , @rory.
preschoolers section image staff section image
note: actual problem was inadvertently writing different same cell , hadn't thought check staffstart variable different way. have therefore decided make answer @jeeped official answer.
define .parent worksheet reference , add after:=.cells(.cells.count)
parameter range.find method.
with sheets("rotas").columns(3) staffstart = .find(what:="staff", after:=.cells(.cells.count), lookat:=xlwhole, _ searchdirection:=xlnext, matchcase:=false).row + 1 end
Comments
Post a Comment