vba - Ending a For-Loop that goes through worksheets in Excel -

-

i appreciate if can following. following code copies range ms excel , paste ms powerpoint. additionally, there loop goes through worksheets of workbook , applies same copy , paste formula. however, i'm struggling how "close" loop when reaches last worksheet. @ end of code, run-time error '91': object variable or block variable not set highlights sh(activesheet.index + 1).select when select debug. 

sub createdeck()  dim wsheet_count integer dim integer dim rng excel.range dim pptapp powerpoint.application dim myppt powerpoint.presentation dim myslide powerpoint.slide dim myshaperange powerpoint.shape dim sh worksheet  'set wsheet_count equal number of worksheet in active workbook  wsheet_count = activeworkbook.worksheets.count  'around world: loop  = 1 wsheet_count  'copy range excel  set rng = thisworkbook.activesheet.range("a1:a2")  'creat instance powerpoint  on error resume next  'check if powerpoint open  set pptapp = getobject(class:="powerpoint.application")  'clear error between errors  err.clear  'open powerpoint if not open  if pptapp nothing set pptapp = createobject(class:="powerpoint.application")  'handle if powerpoint cannot found  if err.number = 429     msgbox ("powerpoint couldn't found, aborting") exit sub  end if  on error goto 0  'make powerpoint visible , active   pptapp.visible = true pptapp.activate  'create new powerpoint  if pptapp nothing     set pptapp = new powerpoint.application end if  'make new presentation  if pptapp.presentations.count = 0     pptapp.presentations.add end if  'add slide presentation  pptapp.activepresentation.slides.add pptapp.activepresentation.slides.count + 1, pplayoutblank  pptapp.activewindow.view.gotoslide pptapp.activepresentation.slides.count  set myslide = pptapp.activepresentation.slides(pptapp.activepresentation.slides.count)  'copy excel range  rng.copy  'paste powerpoint , position  myslide.shapes.pastespecial datatype:=pppasteenhancedmetafile  set myshaperange = myslide.shapes(myslide.shapes.count)  'set position  myshaperange.left = 0 myshaperange.top = 0 myshaperange.height = 450  'clear clipboard  application.cutcopymode = false  'next worksheet tab  sh(activesheet.index + 1).select  next  end sub

your script great job of looping through worksheets, there built-in collection designed situation.

thisworkbook.worksheets contains worksheets in thisworkbook -- can loop through this:

option explicit public sub loopthroughallworksheets()     dim wks worksheet     each wks in thisworkbook.worksheets         msgbox "on sheet: " & wks.index     next wks end sub

this means can tweak for...next loop work this:

for each sh in thisworkbook.worksheets     'do stuff, like:     'set rng = sh.range("a1:a2")     'etc. next sh

leveraging worksheets collection helps avoid using .select , activesheet, can cause users lot of pain:

how avoid using select in excel vba macros


Comments

Post a Comment

Popular posts from this blog

php - Zend Framework / Skeleton-Application / Composer install issue -

-
i trying create zend framework application using skeleton in netbeans. when run composer install, following error message: "c:\php\php.exe" "c:\composer\composer.phar" "--ansi" "--no-interaction" "update" "--dev" using deprecated option "dev". dev packages installed default now. loading composer repositories package information updating dependencies (including require-dev) requirements not resolved installable set of packages. problem 1 - package requires php >=5.5 php version (5.4.42) not satisfy requirement. problem 2 - installation request zendframework/zendframework 2.5.1 -> satisfiable zendframework/zendframework[2.5.1]. - zendframework/zendframework 2.5.1 requires php >=5.5 -> php version (5.4.42) not satisfy requirement. done. apparently, zend framework only needs php 5.3+ contradicts above. need use php 5.4. my composer.json following: { "name": "
Read more

c# - Better 64-bit byte array hash -

-
i need hash algorithm produces 64-bit hash code ( long ) fewer collisions string.gethashcode() , fast (no expensive calls cryptographic functions). here's implementation of fnv still shows 3% of collisions after testing 2 million random strings. need number way lower. void main() { const string chars = "abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz!#@$%^&*()_+}{\":?><,./;'[]0123456789\\"; const int n = 2000000; var random = new random(); var hashes = new hashset<long>(); int collisions = 0; for(int = 0; < n; i++) { var len = random.next(chars.length); var str = new char[len]; (int j = 0; j < len; j++) { str[j] = chars[random.next(chars.length)]; } var s = new string(str); if(!hashes.add(get64bithash( s ))) collisions++; } console.writeline("collision percentage after " + n + " random strings: " + ((doubl
Read more

python - PyCharm Type error Message -

-
i'm newbie trying out ml/dbn in python (3.4) under pycharm (comm.ed 4.5). following definition def make_thetas(xmin,xmax,n): xs = np.linespace(xmin,xmax,n) widths = (xs[1:] - xs[:-1])/2.0 thetas = xs[:-1] + widths return thetas throws error "class 'tuple' not define ' sub ', '-' operator cannot used on instance" on - operator on third line (widths = ....) ideas on how code running under pycharm - works alright in interactive python window. thx. to all, after g'g lot have found workaround seems work within pycharm. workaround because (even python newbie) expected python not need explicit typecasting, not when using datatypes imported lib such numpy. def make_thetas(xmin,xmax,n): xs = np.array(np.linspace(xmin,xmax,n)) widths = (xs[1:] - xs[:-1])/2.0 thetas = xs[:-1]+ widths return thetas using docstrings type-hinting such below did not work def make_thetas(xmin,xmax,n): "&q
Read more