excel - No Object Variable Set when using multiple range.find and findnext() -


i trying search persons' name within range.find keep getting run-time error 91 - object variable or block variable not set. happens "rngfound" within "getpaid".

sub emailclick()  dim lastseasonrow double lastseasonrow = worksheets("season 2014-2015").range("a" & worksheets("season 2014-2015").rows.count).end(xlup).row  dim lastseasonemailrow1 double lastseasonemailrow1 = worksheets("email").range("a" & worksheets("email").rows.count).end(xlup).row  dim rng range dim rngfound range dim getpaid range dim erroremail string dim colmycol new collection 'our collection  j = 2 lastseasonrow     set rng = worksheets("email").range("a2:a" & lastseasonemailrow1)     set rngfound = rng.find(worksheets("season 2014-2015").cells(j, 1).value)     if not rngfound nothing         ' if found         if doesitemexist(colmycol, rngfound.offset(0, 1).value) = false             'check if completed swimmer's family             dim countswimmers string             countswimmers = application.countif(worksheets("email").range("c2:c" & lastseasonemailrow1), rngfound.offset(0, 2).value)             if countswimmers > 1                 s = 1 countswimmers                     if s = 1                         'if first swimmer                         set rng = worksheets("email").range("c2:c" & lastseasonemailrow1)                         set rngfound = rng.find(rngfound.offset(0, 2).value)                         debug.print rngfound.offset(0, -2).value                         set rngbfound = rngfound                     else                         'next swimmer in family                         set rngfound = rng.findnext(rngfound)                         debug.print rngfound.offset(0, -2).value ********************** when debugging, above line highlighted.                     end if ********************************' todo: grab worksheet's name persons' name , money column**                          set getpaid = worksheets("season 2014-2015").range("a2:a" & lastseasonrow).find(rngfound.offset(0, -2).value)                         if not getpaid nothing                             'if found                             if getpaid.offset(0, 14).value <> ""                                 'if owe money                                 debug.print getpaid.offset(0, 14).value                             else                             end if                         end if                 next s                  'write name list, if name in array skip it, when lastseasonrow, remove array.                 colmycol.add (rngfound.offset(0, -1).value)                   'todo: change values below strings correspond aboves combined values                 if rngfound.value = "michael"                     call send_email_using_vba(rngfound.offset(0, 2).value, rngfound.offset(0, 1).value, rngfound.value, worksheets("season 2014-2015").cells(j, 15).value)                 end if             else                 debug.print rngfound.value                 if worksheets("season 2014-2015").cells(j, 15).value <> ""                 'if owe money                     if rngfound.offset(0, 3).value <> ""                     'if multiple emails (primary , cc)                         if rngfound.value = "michael"                             call send_email_using_vba(rngfound.offset(0, 2).value, rngfound.offset(0, 1).value, rngfound.value, worksheets("season 2014-2015").cells(j, 15).value, rngfound.offset(0, 3).value)                         end if                     else                         if rngfound.value = "michael"                             call send_email_using_vba(rngfound.offset(0, 2).value, rngfound.offset(0, 1).value, rngfound.value, worksheets("season 2014-2015").cells(j, 15).value)                         end if                     end if                 end if             end if         end if     else         erroremail = erroremail + worksheets("season 2014-2015").cells(j, 1).value + vbnewline     end if next j  if erroremail <> ""     msgbox ("no email found for: " & vbnewline & erroremail) end if  end sub

thank you

edit: added images data reference:

email worksheet email worksheet

season 2014-2015 worksheet season 2014-2015 worksheet

for proposed solution, need change getpaid variable type long , add variable (e.g. gotpaid) of type double.

dim getpaid long, gotpaid double

change following section of code.

    set getpaid = worksheets("season 2014-2015").range("a2:a" & lastseasonrow).find(rngfound.offset(0, -2).value)     if not getpaid nothing         'if found         if getpaid.offset(0, 14).value <> ""             'if owe money             debug.print getpaid.offset(0, 14).value         else         end if     end if

to this.

    worksheets("season 2014-2015")         gotpaid = application.sumifs(.columns("o"), .columns("a"), rngfound.offset(0, -2).value)         getpaid = application.countifs(.columns("a"), rngfound.offset(0, -2).value)         if cbool(getpaid)             'if found             if cbool(gotpaid)                 'if owe money                 debug.print rngfound.offset(0, -2).value & ": " & gotpaid             else             end if         end if     end

by shuffling off second find operation, not redefining first , .findnext should keep operating until meet countswimmers number. alternately not reusing same variables worksheet functions should work here.

due speciality helper functions doesitemexist not tested compile.


Comments

Post a Comment

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

i have following sample code used pre-decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(--c); cout << c << endl; return; } } this function give output input 4 : dp3 dp2 dp1 dp0 0 1 2 3 but when use post decrement void function(int c) { if(c == 0) { return; } else { cout << "dp" << c << endl; function(c--); cout << c << endl; return; } } output goes in infinite loop dp4 can explain me in detail why happens ? this happens because function(c--) called same value of c , when finishes c decremented. recursively called, hence called same value no chance of return , hit stack overflow error. assume this, int x = 1, y = 0; y = x++ result y == 1 , x == 2. if y = ++x , bot x , y 2.
Read more

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

i added new project. made mistake in projectname.conf file in sites-available apache2, led me reinstalling apache2. after reinstalling , re-enabling projects on every page of every project .. <body> run(); </body> .. i've run composer update, restarted service, restarted pc, checked config files, tried disabling new project. there nothing in error log, , access.log: 127.0.0.1 - - [15/may/2015:01:40:47 +0200] "get / http/1.1" 304 181 "-" "mozilla/5.0 (x11; ubuntu; linux x86_64; rv:38.0) gecko/20100101 firefox/38.0" maybe i'm forgetting apache2 reinstall? don't think did different last time installed it. looks haven't setup php correctly. take @ this great guide , maybe you're forgetting link php config apache2.
Read more

php - How can I echo out this array? -

i don't know how go grabbing data in variable , put foreach loop. class tree{ private $info = array( array('name'=>'jim','state'=>'nu','company'=>'nu','phone'=>array('cell'=>'5615111111','office'=>'5611111111'),'email'=>array('primary'=>'exs@example.com','ex@there.com')), array('name'=>'joe smith','city'=>'phoenix','phone'=>'4805551111','email'=>'jsmith@some_email.com'), array('name'=>'john doe','city'=>'chandler','company'=>'doe co.','email'=>array('jdoe@gmail.com','personal'=>'email@email.com'),'phone'=>'6025550002') ); } if you're inside of class, can access private variable using $this->variablename . example, i...
Read more