vba - Excel: If the above statement is true, then skip the next x number of lines (or go to that specific one) -

strong textgood afternoon,

i have spent few months on pretty lengthy macro project @ work , close end keep encountering errors, because macro has run on reports lot of data gaps (grrrr).

so have list of if conditions. , macro runs them on each cell of column, before going next row. problem creates error (please see code below)

dim degreecol integer   degreecol = ws.rows(1).find("degree").column   dim graddatecol integer   graddatecol = ws.rows(1).find("graduation date (mm/dd/yyyy)").column dim bsgraddatecol integer bsgraddatecol = ws.rows(1).find("b.s. graduation date").column row = 2 endrow     if ws.cells(row, graddatecol).value = ""         ws.cells(row, bsgraddatecol).value = "no data"     end if     if ws.cells(row, degreecol).value = "" , ws.cells(row, graddatecol).value <> ""          ws.cells(row, bsgraddatecol).value = ws.cells(row, graddatecol).value     end if     if ws.cells(row, degreecol).value = "bachelor's degree (±16 years)"         ws.cells(row, bsgraddatecol).value = ws.cells(row, graddatecol).value     end if     if ws.cells(row, degreecol).value = "doctorate degree on (±19 years)"         ws.cells(row, bsgraddatecol).value = dateadd("yyyy", -3, ws.cells(row, graddatecol).value)     end if     if ws.cells(row, degreecol).value = "master's degree (±18 years)"         ws.cells(row, bsgraddatecol).value = dateadd("yyyy", -2, ws.cells(row, graddatecol).value)     end if     if ws.cells(row, degreecol).value = "non degree program (±14 years)"         ws.cells(row, bsgraddatecol).value = dateadd("yyyy", 2, ws.cells(row, graddatecol).value)     end if     if ws.cells(row, degreecol).value = "associate's degree college diploma (±13 years)"         ws.cells(row, bsgraddatecol).value = dateadd("yyyy", 3, ws.cells(row, graddatecol).value)     end if     if ws.cells(row, degreecol).value = "technical diploma (±12 years)"         ws.cells(row, bsgraddatecol).value = dateadd("yyyy", 4, ws.cells(row, graddatecol).value)     end if     if ws.cells(row, degreecol).value = "" , ws.cells(row, graddatecol).value <> ""         ws.cells(row, bsgraddatecol).value = ws.cells(row, graddatecol).value     end if next row   

so have 3 columns: degree, graduation date , b.s. graduation date. ask macro fill in b.s. graduation date column taking , changing value graduation date column depending on degree column says. , because have no control on report , can change go column headers rather coordinates.

now, said problem comes fact each row macro checks every of these , rows have data gaps. example 1 row has "master's degree" in degree column no dates. macro apply first line , fill in b.s. grad date cell "no data" when gets "master's degree" line stops , gives me error because there no date substract 2 years to.

i thought , tried different statements (if else example) still think best way find verbiage macro consider if above if statement true, ignore other statements , go next row. seemed easy @ first far have been unable include solution in macro.

could provide help/advice/suggestions/criticism on that?

thanks!

sorry, need this: wrong approach! 1 of best programming practices define custom method if piece of code used several times. 1 is: work on data, not on "excel-cells".

i'd suggest use dictionary object function:

'requires reference ms scripting runtime.dll function getgraduationdate(byval sgraddate string, byval sdescription string) variant  'date if possible dim dic dictionary  set dic = new dictionary dic.add "bachelor's degree (±16 years)", 0 dic.add "doctorate degree on (±19 years)", -3 dic.add "master's degree (±18 years)", -2 dic.add "non degree program (±14 years)", 2 dic.add "associate's degree college diploma (±13 years)", 3 dic.add "technical diploma (±12 years)", 4  if not isdate(sgraddate) or sdescription = ""     getgraduationdate = "no data" else     getgraduationdate = dateadd("yyyy", dic(sdescription), cdate(sgraddate)) end if  end function 

usage:

sub dowhatever()      ...          ws.cells(row, bsgraddatecol) = getgraduationdate(ws.cells(row, degreecol), _            wsh.cells(row, graddatecol))      next      end sub 

where trick?

if graddate proper date , description not empty string, function looks dictionary object , returns value corresponding description.

got it?