excel - How can I limit the number of loops in a Do While loop? -


so i've written function that uses text-box inputs search corresponding values in sheet. problem if doesn't find match goes infinite loop. can limit loops doesn't crash? if there solution rather limiting loops, i'm ears. here's i'm working with:

function most_recent_deployment(label1 string, label2 string, label3 string) long     dim all_rows range     dim row range     dim lastcell range     dim lastcellrownumber long     set lastcell = sheet7.cells(sheet7.rows.count, "a").end(xldown).end(xlup)     lastcellrownumber = lastcell.row + 1     set row = sheet7.range("a:a").find(label1, lookin:=xlvalues, after:=cells(lastcellrownumber, "a"), searchdirection:=xlprevious)     while row.row > 1          if (sheet7.cells(row.row, 2).text = label2) , (sheet7.cells(row.row, 3).text = label3)             most_recent_deployment = row.row             exit function         end if         lastcellrownumber = row.row         set row = sheet7.range("a:a").find(label1, lookin:=xlvalues, after:=cells(lastcellrownumber, "a"), searchdirection:=xlprevious)      loop     most_recent_deployment = 0 end function 

you add 'and' piece 'do while' loop counter inside loop indicate when have reached end of data in spreadsheet.

something like:

dim counter integer     while row.row > 1 , counter > worksheetfunction.counta(sheet7.range("a:a")      if (sheet7.cells(row.row, 2).text = label2) , (sheet7.cells(row.row, 3).text = label3)         most_recent_deployment = row.row         exit function     end if     lastcellrownumber = row.row     set row = sheet7.range("a:a").find(label1, lookin:=xlvalues, after:=cells(lastcellrownumber, "a"), searchdirection:=xlprevious)     coutner=counter+1  loop 

i mean, yes, it's bit basic, think think job you're looking without having change code much. hope helps.

edit:

lastcellrownumber=row.row temp2=temp1 temp1=lastcellrownumber  set row = sheet7.range("a:a").find(label1, lookin:=xlvalues, after:=cells(lastcellrownumber, "a"), searchdirection:=xlprevious)  if row.row=temp1 , temp1=temp2     exit end if 

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -