excel - Error while using count -


i have sheet "result" , trying count number of "green", "red" , "" values in column "k" of sheet. printing value in sheet "status". in sheet status have table column week number. if weeks in column of sheet "status" same weeknumber in sheet "result" of column o, start counting values in column k

i have code working, lost, due somereason, count value receive not correct one. eg "green" have 73 rows green in column k of result. see printed in sheet "status" 71.

could figure going wrong ? 

sub result() dim integer dim j integer dim cnt integer dim cntu integer dim sht worksheet dim totalrows long set sht = sheets("status") sheets("result").select totalrows = range("e5").end(xldown).row n = worksheets("result").range("e5:e" & totalrows).cells.specialcells(xlcelltypeconstants).count = 2 worksheetfunction.count(sht.columns(1)) cntt = 0 cntu = 0 cnts = 0 if sht.range("a" & i) = val(format(now, "ww")) exit next  j = 5 worksheetfunction.counta(columns(17))  if sht.range("a" & i) = range("q" & j) , range("k" & j) = "green" cntt = cntt + 1  if sht.range("a" & i) = range("q" & j) , range("k" & j) = "red" cntu = cntu + 1  if sht.range("a" & i) = range("q" & j) , range("f" & j) = "" cnts = cnts + 1 if cntt <> 0 sht.range("c" & i) = cntt if cntu <> 0 sht.range("d" & i) = cntu if cnts <> 0 sht.range("b" & i) = cnts if n <> 0 sht.range("g" & i) = n next j if cntr + cntu <> 0 'sht.range("d" & i) = cntr / cntu * 100 end if end sub

i worked way through code , found irregularities in loops. variables , j seem counting both rows , valid rows. therefore renamed these variables make clear rows. also, code tests each row red, green , "". think can 1 of these. therefore, if 1 match other 2 can't be. can lead double counting. finally, found seem writing final result status sheet, in same cells, many, many times.

i'm sorry, following code isn't tested because have no data. have tried address above problems.

option explicit  sub myresult()          ' "result" word reserved use of  vba      dim cntt integer, cntu integer, cnts integer     dim wsstatus worksheet, wsresult worksheet     dim totalrows long     dim rs integer, rr long               ' rowcounters: status & result     dim n integer      set wsstatus = sheets("status")     set wsresult = sheets("result")     totalrows = range("e5").end(xldown).row     n = wsresult.range("e5:e" & totalrows).cells.specialcells(xlcelltypeconstants).count '   improper counting: rs not aligned row number: '    rs = 2 worksheetfunction.count(wsstatus.columns(1))      rs = 2 totalrows         if wsstatus.cells(rs, "a").value = val(format(now, "ww")) exit '        if wsstatus.range("a" & rs) = val(format(now, "ww")) exit     next rs  '   improper counting: rr not aligned row number: '     rr = 5 worksheetfunction.counta(columns(17))      wsstatus         rr = 5 totalrows             if (.cells(rs, "a").value = .cells(rs, "q").value)                 if (.cells(rs, "k").value = "green")                     cntt = cntt + 1                 elseif (.cells(rs, "k").value = "red")                     cntu = cntu + 1                 else                      if (.cells(rs, "a").value = "") cnts = cnts + 1                 end if             end if         next rr     end      wsresult.rows(rs)         ' better write 0 these cells         ' if don't want show 0, format cell hide zeroes         .cells(2).value = iif(cnts, cnts, "")       ' 2 = b         .cells(3).value = iif(cntt, cntt, "")       ' 3 = c         .cells(4).value = iif(cntu, cntu, "")       ' 4 = d         .cells(7).value = iif(n, n, "")             ' 7 = g     end     '    if cntr + cntu <> 0                   ' cntr isn't defined         'wsstatus.range("d" & rs) = cntr / cntu * 100     end if end sub

i urge use option explicit @ top of sheet , declare every variable use.


Comments

Post a Comment

Popular posts from this blog

node.js - Node js - Trying to send POST request, but it is not loading javascript content -

i have been trying send post request website, , has not been loading full site. i guessing problem node js not loading javascript content, executing it. can see javascript in response, though never loads content javascript files producing in html. what mean is, doesn't execute javascript. how make node js code overcome this, , wait the full website load? have tried native code, , "request' module, both producing same errors. if helps, don't need native javascript. modules fine. some code have tried (request module): var options = { method: 'post', uri: 'https://url', formdata: { 'email': 'email', 'prize': '0', 'transactional': 'on' }, headers: { /* 'content-type': 'application/x-www-form-urlencoded' */ // set automatically } }; rp(options) .then(function (body) { console.log(body) }) .catch(function (err) { console.log(err) }); when fetch content...
Read more

javascript - Replicate keyboard event with html button -

i have tried few ways make happen. have javascript pinball game uses keyboard keys controls. converting application touch screen display. workaround, hoping overlay buttons when clicked simulate keyboard strokes. have done research , sounds should work. using jquery v3.2.1 here things have tried far... html element: <input type="button" class="coil"></input> $('.coil').trigger({type: 'keydown', which: 40, keycode: 40}); with this, not errors, nothing happens. var coilkey = $('body').keydown(function(e) { switch (e.keycode) { case 40: break;} }) $('.coil').click(coilkey); this 1 returns error in console... ((jquery.event.special[handleobj.origtype] || (intermediate value)).handle || handleobj.handler).apply not function below javascript game engine handles key listeners... addkeylistener: function (keyandlistener) { this.keylisteners.push(keyandlistener); }, // gi...
Read more

javascript - Web audio api 5.1 surround example not working in firefox -

i've been playing lott lately web audio api on both firefox , chrome. few days ago started experiment surround set. when trying surround audio in web audio api came notice example works fine in chrome v59 not in firefox v54. // create web audio api context var audioctx = new (window.audiocontext || window.webkitaudiocontext)(); audioctx.destination.channelinterpretation = 'discrete'; audioctx.destination.channelcountmode = 'explicit'; audioctx.destination.channelcount = 6; var oscillators = []; var merger = audioctx.createchannelmerger(6); console.log(audioctx.destination, merger); //merger.channelinterpretation = 'discrete'; //merger.channelcountmode = 'explicit'; //merger.channelcount = 6; var addoscilator = function(channel, frequency) { var oscillator = audioctx.createoscillator(); oscillator.frequency.value = frequency; // value in hertz oscillator.connect(merger,0,channel); oscillator.start(); oscillators.push(o...
Read more