VBA in Excel to search for string over multiple tabs and return locations -


i have formed code searching problem trying find multiple results. return first location of string on each tab moves on. when implement while loop commented out appears find first result , escape loop.

i'm not sure if there quirk vba loops missing or while check not quite right have tried debug breaking down , using message boxes no avail besides narrowing believe issue in while loop code.

public function getsearcharray(strsearch string) string dim strresults string dim sht worksheet dim rfnd range dim sfirstaddress range each sht in thisworkbook.worksheets     'msgbox "looping on worksheets"     set rfnd = nothing      sht.usedrange         'msgbox "searching for" & strsearch          set rfnd = .cells.find(what:="*" & strsearch & "*", lookin:=xlvalues, lookat:=xlpart, searchorder:=xlrows, searchdirection:=xlnext, matchcase:=false)         if not rfnd nothing              'save first result can exit loop             if sfirstaddress nothing                 msgbox "we have result , sfirstaddress nothing"                 set sfirstaddress = rfnd             end if               'need loop within sheet keep finding results             'while (not rfnd nothing) , rfnd <> sfirstaddress                  'deal results , build string                 if strresults = ""                     'msgbox "no prev results"                     strresults = "worksheet(" & sht.index & ").range(" & chr(34) & rfnd.address & chr(34) & ")"                     msgbox "first result " & strresults                 else                     strresults = strresults & "|" & "worksheet(" & sht.index & ").range(" & chr(34) & rfnd.address & chr(34) & ")"                     msgbox strresults                 end if                  set rfnd = .findnext(rfnd)              'wend          end if     end msgbox "end sheet loop.." next msgbox "finished going on sheets" msgbox strresults end function

something should work you:

public function getsearcharray(strsearch string) string      dim ws worksheet     dim rngfound range     dim strfirst string     dim strwsname string      each ws in activeworkbook.sheets         set rngfound = ws.usedrange.find(strsearch, ws.usedrange.cells(ws.usedrange.cells.count), xlvalues, xlpart)         if not rngfound nothing             strwsname = ws.name             if instr(1, ws.name, " ", vbtextcompare) > 0 strwsname = "'" & strwsname & "'"             strfirst = rngfound.address                             getsearcharray = getsearcharray & "|" & strwsname & "!" & rngfound.address                 set rngfound = ws.usedrange.find(strsearch, rngfound, xlvalues, xlpart)             loop while rngfound.address <> strfirst         end if     next ws      if len(getsearcharray) > 0 getsearcharray = mid(getsearcharray, 2)  end function

then call function, use this:

sub tgr()      msgbox replace(getsearcharray("test"), "|", chr(10))  end sub

Comments

Post a Comment

Popular posts from this blog

Magento/PHP - Get phones on all members in a customer group -

i working on custom magento extension. working around adminhtml part , i've created custom form there. here form code: <?php class vivasindustries_smsnotification_block_adminhtml_sms_sendmass_edit_form extends mage_adminhtml_block_widget_form { public function _preparelayout() { $extensionpath = mage::getmoduledir('js', 'vivasindustries_smsnotification'); $head = $this->getlayout()->getblock('head'); $head->addjs('jquery.js'); $head->addjs('vivas.js'); return parent::_preparelayout(); } protected function _prepareform() { $form = new varien_data_form(array( 'id' => 'edit_form', 'action' => $this->geturl('*/*/save', array('id' => $this->getrequest()->get...
Read more

php - Bypass Geo Redirect for specific directories -

i'm trying bypass redirect specific url within site. wondering if might able help. the code below code use, original urls replaces example.com i'd able access http://example.com/admin without redirect occuring. is there way this, , there way using own ip? function country_geo_redirect() { $country = getenv('http_geoip_country_code'); if ($country == "us" ) { wp_redirect("http://usa.example.com/"); exit; } else if ($country == "gb" ) { wp_redirect("http://uk.example.com/"); exit; } } add_action('init', 'country_geo_redirect'); you can bypass redirection code if server found ip address. if ip address == <my ip address> ' nothing here else country_georedirect() end if
Read more

php - .htaccess mod_rewrite for dynamic url which has domain names -

i want convert url ex:( example.com/link.php?url=facebook.com ) ( example.com/facebook.com.php ) how can using .htaccess. or there other process make job easier ? i tried adding script .htaccess file options +followsymlinks rewriteengine on rewriterule url-(.*)\.htm$ link.php?url=$1 but converting url adding "url-" ( example.com/url-facebook.com.php ) i want remove "url-" , want make ( example.com/facebook.com.php ) rewriteengine on rewritecond %{the_request} ^get.*link\.php\?url\= [nc] rewritecond %{request_uri} !/system/.* [nc] rewriterule (.*?)link\.php\?url\=/*(.*) /$1$2 [r=301,ne,l] not sure how above work edit of used use remove index.php url
Read more