excel vba - Having Trouble passing a Cell object? (i could be wrong) -

-

first off thank much. on last few months (i believe) coding has progressed drastically. , criticize welcome (rip me apart).

recently started try use different subs (i dont quite understand when use functions etc, figure structure practice when figure out.

i hitting run-time 424 error following bit of code in sub ownercheck

sub occupationnormalization()      dim infobx string      ' initialize variables     lrow = activesheet.usedrange.rows.count     lcol = activesheet.usedrange.columns.count     statuscounter = lrow          while infobx = ""             infobx = inputbox("enter occupation column", "occupation column")         loop                restaurcheck (infobx)  application.screenupdating = true application.statusbar = "" end sub

-

sub restaurcheck(infobx string)     dim restaurants(), restaurantdqs() variant     dim i, lrow, lcol, statuscounter long     dim rrng range          lrow = activesheet.usedrange.rows.count         lcol = activesheet.usedrange.columns.count         statuscounter = lrow restaurants = array("estaur", "food", "cafe", "beverage", "waiter", "waitr", _                 "waitstaff", "wait staff", "grill") 'array list of target occupations   restaurantdqs = array("fast", "pub", "import", "packing", "processing", "packag", _                 "retired", "anufact", "distrib") ' disqualifying words restaurante category  set rrng = range(infobx & "2:" & infobx & lrow)  application.screenupdating = false     each cell in rrng         ownercheck (cell)         = lbound(restaurants) ubound(restaurants)                 if instrrev(cell.value, ucase(restaurants(i))) > 0                     cell.offset(, 1) = "restaurants"                     cell.interior.color = 52479                 end if             debug.print cell.value         next          = lbound(restaurantdqs) ubound(restaurantdqs)             if instrrev(cell.value, ucase(restaurantdqs(i))) , cell.interior.color = 52479                 cell.interior.color = 255                 cell.offset(, 1) = ""               end if          next                      statuscounter = statuscounter - 1                     application.statusbar = "remaining rows " & statuscounter & "           tristram "      next cell   end sub

-

sub ownercheck(str_owner range)    dim owner() variant    owner() = array("owner", "shareholder", "owns ")         = lbound(owner) ubound(owner)                 if instrrev(str_owner, ucase(owner(i))) > 0                     cell.offset(, 2) = "owner"                  end if         next  end sub

i can see couple of issues in ownercheck():

  • "cell" not defined (unless it's global)
  • you shouldn't use "cell" variable name (internal vba property)
  • check validity of incoming range

.

option explicit  sub ownercheck(byref rngowner range)      if not rngowner nothing          dim owner() variant         owner() = array("owner", "shareholder", "owns ")          = lbound(owner) ubound(owner)             if instrrev(ucase(rngowner), owner(i)) > 0                 rngowner.offset(, 2) = "owner"             end if         next      end if  end sub

Comments

Post a Comment

Popular posts from this blog

php - Admin SDK -- get information about the group -

-
please, help. https://www.googleapis.com/admin/directory/v1/groups/106274944935548788283 sending request obtain information group id 1062 ... here link method in google https://developers.google.com/admin-sdk/directory/v1/guides/manage-groups#get_group in response error - 401 unauthorized. oauth authenticate via user has received rights necessary request, included in console admin sdk. authorization successful, there token. not can not understand. if not hard - response. requested information: file_get_contents("https://www.googleapis.com/admin/directory/v1/groups/1062749‌​44935548788283?key=aizasybruhiomdf9nlx9lx3imxwesqmcrnhh4l4"); id group - "106274944935548788283" key applications in case - "aizasybruhiomdf9nlx9lx3imxwesqmcrnhh4l4" error: failed open stream: http request failed! http/1.0 401 unauthorized
Read more

dns - How To Use Custom Nameserver On Free Cloudflare? -

-
i have problem. i own small hosting company. trying add cloudflare site free cdn. problem when sign have change nameservers nameservers. have nameserver dns set through godaddy, how add them cloudflare? main site uses company's nameservers whenever add them site goes offline. when go cloudflare's dns records click on dropdown, click ns, , there's 2 boxes 'name' , 'nameserver'. mean input 'ns1' 'name' , 'ns1.domain.com' 'nameserver'? input server's ip addresses? is there way of accomplishing free version of cloudflare? that impossible. option have custom name servers available on business , enterprise. see https://www.cloudflare.com/plans . also, might mistaken. must change name servers in domain register settings, not on cloudflare.
Read more

Python Error - TypeError: input expected at most 1 arguments, got 3 -

-
can explain why can not use your_name in goal variable? my_name = "bryson" my_age = 29 your_name = input ("what name? ") your_age = input ("what age? ") print ("my name is", my_name,", , am", my_age, "years old.") print ("your name is", your_name,", , are", your_age,".") print("thank buying book,", your_name,"!") goal = input ("what favorite part of book,", your_name, "?") print("awesome!") the error is: goal = input ("what favorite part of book,", your_name, "?") typeerror: input expected @ 1 arguments, got 3 you got error because in fact gave 3 arguments input function when expecting 1 (namely, string prompt). in input ("what favorite part of book,", your_name, "?") ---------------------------------------- , ---------, --- the underlined parts comma-separated arguments: st...
Read more