vba - Calculate Event: Insert Comment In Cell If Value and Delete Any Comments If Not Value -

the following code change event handler searches col b word "fee" , inserts comments in 3 adjacent cols if word "fee" found in col b:

private sub worksheet_calculate()  dim rng range, cell range  set rng = range("b:b")  if not rng nothing      each cell in rng.cells         if cell.value = "fee"             cell.offset(0, 1).addcomment "fi"             cell.offset(0, 2).addcomment "fo"             cell.offset(0, 3).addcomment "fum"         end if     next end if  end sub 

the above code works fine.

i want search col b , delete existing comments in 3 adjacent cols if word "fee" not occur in col b. so, added else statement:

private sub worksheet_calculate()  dim rng range, cell range  set rng = range("b:b")  if not rng nothing      each cell in rng.cells         if cell.value = "fee"             cell.offset(0, 1).addcomment "fi"             cell.offset(0, 2).addcomment "fo"             cell.offset(0, 3).addcomment "fum"         else:             cell.offset(0, 1).comment.delete             cell.offset(0, 2).comment.delete             cell.offset(0, 3).comment.delete          end if     next end if  end sub 

this results in runtime error: "object variable or block variable not set", , debugger points to

cell.offset(0, 1).comment.delete 

vba seems want me use statement, permutations i've tried result in same error. thoughts?

follow andy's correct suggestion. code adds comments if condition met, clears comments if not:

private sub worksheet_calculate()  dim rng range, cell range set rng = range("b:b")  if not rng nothing     each cell in rng.cells         cell.offset(0, 1).clearcomments         cell.offset(0, 2).clearcomments         cell.offset(0, 3).clearcomments         if cell.value = "fee"             cell.offset(0, 1).addcomment "fi"             cell.offset(0, 2).addcomment "fo"             cell.offset(0, 3).addcomment "fum"         end if     next end if  end sub 

vba not suggesting use with. error occurs if attempt delete comment when there isn't one.

you can either check existence of comment before attempting delete it:

dim cmt comment  set cmt = range("a1").comment if not cmt nothing     range("a1").comment.delete end if 

or, simpler, use clearcomments:

range("a1").clearcomments 

also note first code on calculate event, not change.

delete colon after else - have else single word on own line; colon may cause issues.

added following ops coded solution: code simplified:

if not rng nothing      each cell in rng.cells         cell.offset(0, 1).clearcomments         cell.offset(0, 2).clearcomments         cell.offset(0, 3).clearcomments         'or         'cell.range("b1:d1").clearcomments          if cell.value = "fee"             cell.offset(0, 1).addcomment "fi"             cell.offset(0, 2).addcomment "fo"             cell.offset(0, 3).addcomment "fum"         end if     next end if