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

Popular posts from this blog

javascript - Thinglink image not visible until browser resize -

firebird - Error "invalid transaction handle (expecting explicit transaction start)" executing script from Delphi -

mongodb - How to keep track of users making Stripe Payments -