excel - VBA Select last empty cell in Month column using today's date and paste data -


i'm trying use vba script trigger when checkbox checked copies data 1 particular cell , pastes in last empty cell of month column using today's date. here code far, , i've tested check box triggering copy , paste function. can't figure out finding correct column using today's date , selecting next empty cell in column. columns labeled on second sheet using long month names (text data).

sub checkboxupdated() dim mnth string dim fndrng dim cb checkbox   mnth = monthname(month(date)) sheet2 'has 'with' work correctly     set fndrng = cells.find(what:=mnth, after:=a1, _     lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _     matchcase:=true) end      on error resume next     set cb = activesheet.drawingobjects(application.caller)     on error goto 0      if not cb nothing         if cb.value = 1             sheets("sheet1").range(cb.linkedcell).offset(0, -4).copy             sheets("sheet2").activate             fndrng.offset(4, 0).select             activesheet.paste         end if     end if  end sub

any appreciated, thanks!!!!

two things noticed immediately.

  1. within first with...end statement, set fndrng = cells.find ... missing prefix period assigns worksheet parent statement. should set fndrng = .cells.find...

  2. the close of with sheet2 extended down encompass more of code, releasing dependence on things activesheet , select.

consider rewrite.

sub checkboxupdated()     dim mnth string, fndrng range, cb checkbox      on error resume next      mnth = monthname(month(date))      sheet2 'has 'with' work correctly         set fndrng = cells.find(what:=mnth, after:=a1, lookat:=xlpart, _                                 searchorder:=xlbyrows, searchdirection:=xlnext, _                                 matchcase:=true)          set cb = .drawingobjects(application.caller)         on error goto 0          if not cb nothing             if cb.value = 1                 sheets("sheet1").range(cb.linkedcell).offset(0, -4).copy _                     destination:=fndrng.offset(4, 0)             end if         end if      end  end sub

i changed method of copy & paste more direct method in keeping expansion of with/end statement.

see how avoid using select in excel vba macros more methods on getting away relying on select , activate accomplish goals.


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