VBA - "For each"-loop inside for-loop only works the first time -


i trying have "for each"-loop range inside for-loop sheets. works fine first time (for sheet1) during second lap code stops (error 400) between test 1 , 2, have mean in "each-loop". know why?

my declarations (dim directory string, filename string, sheet worksheet, integer, j integer dim x range, y range, urffile string, sheetnbr integer)

for sheetnbr = 1 workbooks(filename).worksheets.count         j = nodataactive() 'see row typing should start on       set x = workbooks(filename).worksheets(sheetnbr).range("a2") 'start active search @ a2     set y = workbooks(filename).worksheets(sheetnbr).range("a2") 'start possible search @ a2     workbooks("utkast1.xlsm").worksheets("active").cells(sheetnbr, 5).value = 1 'test      'for active product numbers in sheet     each x in workbooks(filename).worksheets(sheetnbr).range(cells(1, 1), cells(range("a" & rows.count).end(xlup).row, 1))          workbooks("utkast1.xlsm").worksheets("active").cells(sheetnbr, 6).value = 2 'test          if x.offset(0, 2).value() = "j"             workbooks("utkast1.xlsm").worksheets("active").cells(j, 1).value = workbooks(filename).worksheets(sheetnbr).name 'print store number             workbooks("utkast1.xlsm").worksheets("active").cells(j, 2).value = x.value() 'print bnr             workbooks("utkast1.xlsm").worksheets("active").cells(j, 3).value = "urf" 'print data source             j = j + 1         end if     next x next sheetnbr

why not use 2 for each loops?

be careful name of variable too, sheet element of vba language, rename asheet.

when use workbooks("utkast1.xlsm").worksheets("active"), mean take activesheet or have sheet named active?

for each asheet in workbooks(filename).worksheets     j = nodataactive() 'see row typing should start on     set x = workbooks(filename).worksheets(sheetnbr).range("a2") 'start active search @ a2     set y = workbooks(filename).worksheets(sheetnbr).range("a2") 'start possible search @ a2      set ws = workbooks("utkast1.xlsm").worksheets("active")     ws.cells(asheet.index, 5).value = 1 'test     'for active product numbers in sheet     each x in asheet.range(asheet.cells(1, 1), asheet.cells(asheet.range("a" & asheet.rows.count).end(xlup).row, 1))         ws.cells(sheetnbr, 6).value = 2 'test         if x.offset(0, 2).value() = "j"             ws.cells(j, 1).value = asheet.name 'print store number             ws.cells(j, 2).value = x.value() 'print bnr             ws.cells(j, 3).value = "urf" 'print data source             j = j + 1         end if     next x next asheet  set x = nothing set y = nothing set ws = nothing

Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

c# - Exception when attempting to modify Dictionary -

i creating dictionary manage threads , ran exception when attempt clear dead threads after adding threads dictionary. using system; using system.collections.generic; using system.threading; namespace syscat { public class threadmanager { public static readonly threadmanager globalthreadmanager = new threadmanager( ); /// <summary> /// initializes new instance of <see cref="syscat.threadmanager"/> class. /// </summary> public threadmanager( ) { } /// <summary> /// create new threadmanager threads within basetm , deinitializes basetm /// </summary> public threadmanager( threadmanager basetm ) { this.threads = basetm.threads; basetm.threads.clear( ); } private dictionary<guid,thread> threads = new dictionary<guid, thread>( ); /// <summary> /// attempts obtain unused threadid within ...
Read more