excel - How to loop through rows in table using a function -

i'm creating test plan template work contains table various fields each row being identified "test no" column. when adding test case, want check see if test case has been entered in table.

im attempting using function passes in parameter test case number being entered. want loop through rows , if matching case found, want error message displayed , exit sub. have far doesnt seem work.

function:

function existingtest(x variant) boolean      dim intstartrow     integer     dim intstartcol     integer     dim introwcount     integer     dim intcolcount     integer     dim testtable       string      testtable = "tests"      introwcount = range(testtable).rows.count     intcolcount = range(testtable).columns.count     intstartrow = range(testtable).rows(0).row     intstartcol = range(testtable).columns(1).column      existingtest = false      = 1 introwcount     if range(testtable).rows(i).columns(1).value = x         existingtest = true         exit     end if next   end function 

code in main block:

    if existingtest(tnname)     response = msgbox("error: test evidence test number " + tnname + " exists")     exit sub end if 

i not use loop can use function "countif" count amount of recurrences of id.

also not need special function this, use data validation. data > data validation > custom add following formula, assuming data starts in a2 , ids in column a, adjust ifneeded

=countif(a:a,a2)=1 

that's person able enter unique ids now.

if still want use user defined function @ example uses countif

function existingtest(byval r range, byval strnamedrange string) boolean     existingtest = worksheetfunction.countif(range(strnamedrange).columns(1), r.value)  end function   sub mainblock()      if existingtest(tnname, "test")         response = msgbox("error: test evidence test number " + tnname + " exists")     end if  end sub 

hope helps.

thanks