excel - Issue with Using Contents of Cell Relative to Active Cell as a Condition in a Custom If Function -

i attempting create first user defined function in excel visual basic editor. using excel 2011 on macbook pro 2011 os x el capitan version 10.11.1

i working on creating workbook use functions figure results barrel race in 4 division format. divisions follows:

the first division or "1d" fastest times 0.499 seconds off of fastest time.

the second division or "2d" next fastest times between 0.5 , 0.999 seconds off of fastest time.

the third division or "3d" times between 1 second , 1.499 seconds off of fastest time.

lastly, fourth division or "4d" times 1.5 or more seconds off of fastest time.

this current code have figure these divisions:

function div(pval) if pval = "z" div = "" elseif pval = 999 div = "dq" elseif pval > 100 div = "nt" elseif pval < (range("b2") + 0.5) div = "x1d" elseif activecell.offset(-1, 0) = "x1d" , pval < (range("b2") + 1) div = "2d" elseif pval < (range("b2") + 1) div = "x2d" elseif pval < (range("b2") + 1.5) div = "x3d" elseif pval > (range("b2") + 1.499) div = "x4d" else div = "incorrect" end if end function 

now, want first time in each division result in "2d", "3d" & "4d" , rest of times in each division result in "x1d", "x2d", "x3d" & "x4d" because applying conditional formatting beginning "x" appear in white text , therefore invisible when looking @ it, however, still able use future functions when move on placings in each division.

so example, i'm working on 2d , want times between 0.5 , 0.999 seconds off of fastest time return result of "x2d" except first 1 mean cell above "x1d".

everything in code above working except elseif should returning "2d". coming false , moving on next elseif returns "x2d".

i have removed parts of code 1 @ time , tested them. have narrowed problem down activecell.offset section.

i've searched on internet past 3 days trying figure out i'm missing , can't find it. following ways i've tried typing based on i've found online , far nothing has worked.

elseif activecell.offset(rowoffset:=-1, columnoffset:=0) = "x1d" , pval < (range("b2") + 1) div = "2d"  elseif pval < (range("b2") + 1) , activecell.offset(-1, 0) = "x1d" div = "2d"  elseif pval < (range("b2") + 1) , activecell.offset(-1, 0).select = "x1d" div = "2d"  elseif pval < (range("b2") + 1) , activecell.offset(-1, 0).range = "x1d" div = "2d"  elseif pval < (range("b2") + 1) , activecell.offset(-1, 0).activate = "x1d" div = "2d" 

i have tried 1 other way of coding based on suggested on site , :

function div(pval)  if pval = "z" div = "" if pval = 999 div = "dq" if pval > 100 div = "nt" if pval < (range("b2") + 0.5) div = "x1d" if activecell.offset(-1, 0) = "x1d" , pval < (range("b2") + 1) div = "2d" if pval < 100 div = "correct" if div = "" div = "incorrect"  end function 

when tried 1 made entire function stop working , results came "correct".

i @ complete loss should fix , if has suggestions appreciative.

thank you!

edit** function used in of column c except c1 & c2 because c1 header cell , c2 1d.

specific example of how function used:

let's there 12 times, 3 in each division.

the fast time in b2 15.000.

the time in b5 15.5 first 2d time.

the function in c5 , should first ask if b5 = "z", if true should leave cell blank, if not true should ask if b5 = 999, if true should return "dq", if not true should ask if b5 > 100, if true should return "nt", if not true should ask if b5 < b2 + 0.5, if true should return "x1d", if not true should ask if b5 < b2 + 0.999 , c4 = "x1d", if true should return "2d", if not true should ask if b5 < b2 + 0.999, if true should return "x2d", if not true should go on ask next divisions. if can figure out how 2nd division can learn , able finish other divisions , create new functions other things want workbook do.

i hope helps!

you need re-design function's logic, when designing udf pass variables to rather assigning them within function. think function should like:

public function div(pval double, testrng range, testx1d range) string      dim ret string      if testx1d.value = "x1d"         if pval < testrng.value + 1             div = "2d"             exit function         end if     end if      select case cvar(pval)         case "z":                           ret = vbnullstring         case 999:                           ret = "dq"         case > 100:                      ret = "nt"         case < (testrng.value + 0.5):    ret = "x1d"         case < (testrng.value + 1):      ret = "x2d"         case < (testrng.value + 1.5):    ret = "x3d"         case > (testrng.value + 1.499):  ret = "x4d"         case else:                          ret = "incorrect"     end select      div = ret  end function 

---

it's worth noting have specify order of test when comparing values in way. example if value of pval 999 in scenario:

case 999:                           ret = "dq" case > 100:                      ret = "nt" 

it return "dq" because first test, if these other way around:

case > 100:                      ret = "nt" case 999:                           ret = "dq" 

you "nt" because pval also greater 100. bear in mind...

---

to use udf

example, formula entered in c5 pval value in b5

=div(b5,c4,$b$2) 

(note absolute reference b2)