excel - If IsEmpty then go to column A but in same row and extract that data -

i creating spreadsheet has many columns , trying make check if cell in column empty check see cells in other columns of same row empty. way know of if cell checking.

'checking if "email sent columb cells empty can set "no" if isempty(thisworkbook.sheets(2).range("f2")) thisworkbook.sheets(2).range("f2") = "no"   if isempty(thisworkbook.sheets(2).range("j2"))   thisworkbook.sheets(2).range("j2") = "no"     if isempty(thisworkbook.sheets(2).range("m2"))     thisworkbook.sheets(2).range("m2") = "no"     end if end if end if  'if acknowledge email sent = no check if there fields       empty if not send email , mark 'it sent if thisworkbook.sheets(2).range("f2") = "no"  if isempty(thisworkbook.sheets(2).range("a2")) msgbox "a2 empty" else if isempty(thisworkbook.sheets(2).range("b2")) msgbox "b2 empty" else     if isempty(thisworkbook.sheets(2).range("c2"))     msgbox "c2 empty"     else         if isempty(thisworkbook.sheets(2).range("d2"))         msgbox "d2 empty"         else             if isempty(thisworkbook.sheets(2).range("e2"))             msgbox "e2 empty"             else             msgbox "acknowledge email ready sent"             thisworkbook.sheets(2).range("f2") = "yes"             end if         end if     end if end if end if  end if  'if update email sent = no check if further evidence required if     check if evidence required 'not empty, if isnt empty send email , mark sent, if further evidence required set no 'will mark unnecessary. if thisworkbook.sheets(2).range("j2") = "no"  if thisworkbook.sheets(2).range("h2") = "yes" or thisworkbook.sheets(2).range("h2") =  ""  if isempty(thisworkbook.sheets(2).range("h2")) msgbox "h2 empty" else if isempty(thisworkbook.sheets(2).range("i2")) msgbox "i2 empty" else     msgbox "update email ready sent"     thisworkbook.sheets(2).range("j2") = "yes" end if end if else thisworkbook.sheets(2).range("j2") = "unnecessary"  end if  end if  if thisworkbook.sheets(2).range("m2") = "no"  if thisworkbook.sheets(2).range("l2") = "" msgbox "l2 empty" else if thisworkbook.sheets(2).range("l2") = "approved pay" msgbox "approved pay email sent" thisworkbook.sheets(2).range("m2") = "yes" else if thisworkbook.sheets(2).range("l2") = "approved without pay" msgbox "approved without pay email sent" thisworkbook.sheets(2).range("m2") = "yes" else if thisworkbook.sheets(2).range("l2") = "approved in part" msgbox "approved in part email sent" thisworkbook.sheets(2).range("m2") = "yes" else if thisworkbook.sheets(2).range("l2") = "referred line manager" msgbox "approved without pay email sent" thisworkbook.sheets(2).range("m2") = "yes" end if end if end if end if end if end if 

this how have edited below code, doesn't seem working

option explicit sub test3() end sub  public function checkifrowisempty(byval p2 range, _                                   byval colstocheck integer) boolean      dim isrowempty boolean     dim startrow integer     dim endrow integer     dim integer      isrowempty = true      if iscellempty(wb.sheets(2).range("p2")) = true         startrow = p2.column         endrow = p2.column + (13 - 1)          = startrow endrow             if iscellempty(p2.offset(, a)) = false                 isrowempty = false                 exit             end if         next     else         isrowempty = false     end if     debug.print cstr(isrowempty)     checkifrowisempty = cstr(isrowempty)  end function   private function iscellempty(byval p2 range) boolean     if len(p2.value & "") < 1         iscellempty = true     else         iscellempty = false     end if end function 

i believe looking understand range object. when call range("p2") or range("p2:z2"), asking range object.

in short: can think of them collection of variables , methods(functions).

and in vba, use objects variable need set them (a simple equality won't work):

so in case you'd do:

dim rng range set rng = range("a2") 

and if want set else, need set using function returns range object. (e.g. cells(...), offset(...), etc.)

and check if cell empty. you'd ask value property , compare "":

if (rng.value = "")  

and if looking grab cell same row in column a, you'd use cells instead of range.

cells(rng.row,1).value