excel vba - VBA: Checking a number in an array and proceeding when not present -

-

i new vba. in below code, attempting search list of numbers in array in dynamic column. if number found, new line inserted. however, error when number in array not found. if there more efficient way same thing, happy change code. appreciated.

sub insertlines()  dim vbusort variant dim vsheet worksheet dim integer vbusort = array("3", "4", "5", "6", "7", "8", "9", "10")      each vsheet in thisworkbook.worksheets         range("a1").select         application.activesheet.range("1:2").find(what:="bu sort priority", _                     lookin:=xlvalues, _                     lookat:=xlwhole, _                     searchorder:=xlbyrows, _                     searchdirection:=xlnext, _                     matchcase:=false).select          activecell.entirecolumn.select          = lbound(vbusort) ubound(vbusort)              selection.find(what:=vbusort(i), after:=activecell, _                 lookat:=xlwhole, searchorder:=xlbycolumns, searchdirection:=xlnext, _                 matchcase:=false).activate                 activecell.entirerow.insert                 activecell.entirerow.interior.color = 6697728          next         end     next vsheet  end sub

you need capture result of .find() test see if found anything:

sub insertlines()  dim vbusort variant dim vsheet worksheet dim integer dim rng1 range dim rng2 range   vbusort = array("3", "4", "5", "6", "7", "8", "9", "10")   each vsheet in thisworkbook.worksheets         range("a1").select         set rng1 = application.activesheet.range("1:2").find(what:="bu sort priority", _                     lookin:=xlvalues, _                     lookat:=xlwhole, _                     searchorder:=xlbyrows, _                     searchdirection:=xlnext, _                     matchcase:=false).select         if not rng1 = nothing            activecell.entirecolumn.select             = lbound(vbusort) ubound(vbusort)               set rng2 = selection.find(what:=vbusort(i), after:=activecell, _                 lookat:=xlwhole, searchorder:=xlbycolumns, searchdirection:=xlnext, _                 matchcase:=false)                 if not rng2 = nothing                   rng2.select                     activecell.entirerow.insert                     activecell.entirerow.interior.color = 6697728                 end if             next           end         end if   next vsheet  end sub

also, how avoid using select in excel vba macros


Comments

Post a Comment

Popular posts from this blog

javascript - gulp-nodemon - nodejs restart after file change - Error: listen EADDRINUSE events.js:85 -

-
i'm running nodejs 0.12.3 - gulp 3.10.8 , gulp-nodemon 2.0.3. gulpfile.js : gulp.task('serve', ['build'], function() { gulp.start('fb-flo'); nodemon({ script: paths.server, env: { 'node_env': 'development' }, // env: { 'node_env': 'production' }, execmap: { 'js': 'node_modules/babel/bin/babel-node --stage 1' //es7 cote server }, delay: '0ms', watch: ['src/shared/', 'src/server/'], ignore: ['src/shared/components'], tasks: function (changedfiles) { var tasks = []; changedfiles.foreach(function (file) { if (path.extname(file) === '.jsx' && !~tasks.indexof('bundlejs')) tasks.push('bundlejs'); if (path.extname(file) === '.less' && !~tasks.indexof('compileless')) tasks.push('compileless'); }); return tasks; } ...
Read more

Fatal Python error: Py_Initialize: unable to load the file system codec. ImportError: No module named 'encodings' -

-
i trying make simple python program opening list of webpages user manually download reports site. don't have previous experience preparing exe files.. , i'm in learning process python coding. of done on windows 7 x64 this python code: #!c:/python34/python.exe -u splinter import * import time import os import csv #---------------------------------- raporty = [] open('../raporty.csv', newline='') csvfile: contents = csv.reader(csvfile, delimiter=' ', quotechar='|') row in contents: r = ', '.join(row) r = r.replace(',','') raporty.append(r) #--not implemented yet zmienne = [] open('../zmienne.csv', newline='') csvfile: contents = csv.reader(csvfile, delimiter=' ', quotechar='|') row in contents: r = ', '.join(row) r = r.replace(',','') zmienne.append(r) print("start") browser = browser(...
Read more

javascript - oscilloscope of speaker input stops rendering after a few seconds -

-
the following script reads audio user's microphone , renders oscilloscope on html canvas. the source taken example of mozilla developer network: visualizations web audio api and here fiddle: http://jsfiddle.net/b7j8pktp/ mozgetusermedia (note: code has no fork mechanism different browsers: works firefox) it works fine few seconds , stops rendering. whereas works totally stable: http://mdn.github.io/voice-change-o-matic/ the problem can reduced following code. microphone activation icon (next the address bar in firefox) disappears after 5 seconds: navigator.mozgetusermedia({audio: true}, function() {}, function() {} ); ( http://jsfiddle.net/b7j8pktp/2/ ) this known bug in firefox. take stream getusermedia call , hook window so: navigator.mozgetusermedia({audio: true}, function(stream) { window.stream = stream; // rest of code }, function err() { // handle error }); hopefully can fixed soon. problem we're f...
Read more