excel - Proper use of offset function and for/while loop -

bit new vba. so, forgive me if it's pretty simple. making excel form various companies' registration.

my excel records first column(a) company_name 1 has give his/her company_name.

column c has total no. of employees in company. (this includes permanent + contract employees).

column d , e has employees' names permanent only.

so, assume; column c has value 5; column (d , e) may have 5 or less 5 employee names. (strictly not more 5 employee names. if columns have more 5 names; should throw error message in sheet2)

my next column f designation. can have values among 'manager','attender','developer','operator','ceo' , no other value allowed. these values can repeated (for different employees in below image)

for have written below code , works fine.

dim  celadr, celval, celadri, celvali, celadrn, celvaln variant  range("f2:f" & lastrow).select     ' selection.clearformats     each cell in selection         celadr = cell.address         celval = cell.value         celadri = cell.offset(0, 4).address         celvali = cell.offset(0, 4).value         celadrn = cell.offset(0, 9).address         celvaln = cell.offset(0, 9).value      if len(celval) >= 1         if instr(1, ("manager,attender,developer,operator,ceo,"), trim(ucase(celval) & ",")) < 1             range(celadr).interior.color = vbred              sheets("sheet2").range("a65536").end(xlup).offset(1, 0).value = celval & "  designation incorrect"             strstr = "'" & shname & "'!" & range(celadr).address(0, 0)             sheets("sheet2").hyperlinks.add anchor:=sheets("sheet2").range("a65536").end(xlup), address:="", subaddress:=strstr          end if     end if            next 

now next requirement is; each company, there should atleast single 'manager'.

if company don't have manager, have give message in 'sheet2' "their should atleast single 'manager' company" link correct cells in sheet1.

i able validate when 'manager' written in same row in company_name written.

but, don't have idea how when 'manager' not written in same row of company_name.

below code works when designation - 'manager' assumed written in same row of company_name.

range("a2:a" & lastrow).select      each cell in selection         celadr = cell.address         celval = cell.value     if len(celval) >= 1              if trim(cell.offset(i, 5).value) <> "manager"       cell.offset(i, 5).interior.color = vbred      sheets("sheet2").range("a65536").end(xlup).offset(1, 0).value = celval & "their should atleast single 'manager' company"     strstr = "'" & shname & "'!" & range(celadr).address(0, 0)     sheets("sheet2").hyperlinks.add anchor:=sheets("sheet2").range("a65536").end(xlup), address:="", subaddress:=strstr     end if      end if     next 

i tried write code below validate when 'manager' written on different row company_name.

(i have commented part giving me error.)

range("a2:a" & lastrow).select ' selection.clearformats each cell in selection     celadr = cell.address     celval = cell.value if len(celval) >= 1         ' = 0 cell.offset(0, 2).value         if trim(cell.offset(i, 5).value) <> "manager" 'and cell.offset(1, 0).value < 1         ' nothing ' else cell.offset(i, 5).interior.color = vbred  sheets("sheet2").range("a65536").end(xlup).offset(1, 0).value = celval & "their should atleast single 'manager' company" strstr = "'" & shname & "'!" & range(celadr).address(0, 0) sheets("sheet2").hyperlinks.add anchor:=sheets("sheet2").range("a65536").end(xlup), address:="", subaddress:=strstr end if  ' next  ' if cell.offset(1, 0).value >= 1 ' end if  end if  next 

can guide me how can make proper use of offset function , loop/while loop/find function verify each company has atleast single manager?

when trying determine if missing or not generaly easier set boolean use afterwards apply correct procedure.

this should :

dim hasmanager boolean, _     lastrow long lastrow = activesheet.range(rows.count).end(xlup).row  range("a2:a" & lastrow).select 'selection.clearformats  each cell in selection     celadr = cell.address     celval = cell.value     if len(celval) >= 1         hasmanager = false         'for = 0 cell.offset(0, 2).value         = 0         while hasmanager = false , _         (cell.offset(i, 0).value = "" or cell.offset(i, 0).value = celval) , _         cell.offset(i, 0).row <= lastrow + 1             if lcase(trim(cell.offset(i, 5).value)) = "manager" hasmanager = true             = + 1         loop          if hasmanager             'the company has @ least 1 manager         else             'the company doesn't have manager             cell.offset(i - 1, 5).interior.color = vbred              sheets("sheet2").range("a65536").end(xlup).offset(1, 0).value = celval & "their should atleast single 'manager' company"             strstr = "'" & shname & "'!" & range(celadr).address(0, 0)         end if     else     end if next cell