excel - VBA to sum values from cells between two times -


i'm not enough familiar vba unfortunately have make done.

i got extract warehouse management system looks that: enter image description here

i need count productivity of staff according extract huge.

so prepared xls with 2 additional tabs. first tab contains extract frm above. second tab contains count looks that

enter image description here

there 97 rows start , end times (24 hrs divided 15 minute parts)

third tab contains result looks that:

enter image description here

i have create vba macro count productivity. macro have to:

  • sum how many products picked (scanned rf gun) , store in particular 15 minute part in tab count.
  • sum how many 15 minute parts user scanning product rf guns (4 * 15 minute parts = 1h. example: user extract scanning product 03:21 03:43 in tab count should have sum of cases stored in 03:15 03:30 , 03:30 03:45. hope makes sense) according comodity (there 3 comodities: ambient, chill, frozen) , result have stored in third tab result. if scanning products in 3 different areas should have in result tab 3 different sums 3 comodities.

as said. i'm not familiar vba hope can me. if not have writing filled paper based activity sheets filled warehouse members in excel , counts myself gonna cost me lot of time 100 people.

i appreciate help.

pivot table way go. kick off first summary. need excel 2013 second 1 able select distinct counts 15 minute blocks.

dim wb workbook  dim sourceworksheet worksheet dim summaryworksheet worksheet  dim pvt1 pivottable  dim firstcell range dim lastrow long   set wb = activeworkbook  set sourceworksheet = wb.activesheet   'add 2 columns calculate 15 minute blocks '===================================================  '***** set top-left cell of data contains "hours" label set firstcell = sourceworksheet.range("a4")   'determine last row lastrow = firstcell.currentregion.rows.count + 3  'put column captions on new columns cells(firstcell.row, firstcell.column + 4).value = "start" cells(firstcell.row, firstcell.column + 5).value = "end"  'set formula "start" of 15 minute block range(cells(firstcell.row + 1, firstcell.column + 4), cells(lastrow, firstcell.column + 4)).formula = "=floor(" & cells(firstcell.row + 1, firstcell.column).address(false, false) & ",time(0,15,0))"  'set formula "end" of 15 minute block range(cells(firstcell.row + 1, firstcell.column + 5), cells(lastrow, firstcell.column + 5)).formula = "=" & cells(firstcell.row + 1, firstcell.column + 4).address(false, false) & "+""00:15"""  'format columns times range(cells(firstcell.row, firstcell.column + 4), cells(lastrow, firstcell.column + 5)).numberformat = "hh:mm"   'create pivot table summary 15 minute block '===================================================  set summaryworksheet = sheets.add summaryworksheet.name = "summary"  set pvt1 = wb.pivotcaches.create(sourcetype:=xldatabase, _                                 sourcedata:=sourceworksheet.name & "!" & firstcell.currentregion.address(referencestyle:=xlr1c1), _                                 version:=xlpivottableversion15).createpivottable(tabledestination:=summaryworksheet.name & "!r3c1", _                                                                                  tablename:="pivottable1", _                                                                                  defaultversion:=xlpivottableversion15) pvt1.pivotfields("start")     .orientation = xlrowfield     .position = 1 end with pvt1.pivotfields("end")     .orientation = xlrowfield     .position = 2 end with pvt1.pivotfields("user")     .orientation = xlrowfield     .position = 3 end pvt1.adddatafield pvt1.pivotfields("cases"), "sum of cases", xlsum 'change style tabular pvt1.rowaxislayout xltabularrow 'remove unwanted subtotals pvt1.pivotselect "end[all;total]", xldataandlabel, true pvt1.pivotfields("end").subtotals = array(false, false, false, false, false, false, false, false, false, false, false, false) pvt1.pivotselect "start[all;total]", xldataandlabel, true pvt1.pivotfields("start").subtotals = array(false, false, false, false, false, false, false, false, false, false, false, false) pvt1.pivotselect "start[all]", xllabelonly, true range("a3").select  'be memory citizen set wb = nothing set sourceworksheet = nothing set summaryworksheet = nothing set pvt1 = nothing set pvt2 = nothing set firstcell = nothing

Comments

Post a Comment

Popular posts from this blog

c# - Validate object ID from GET to POST -

i have method allows user access comment have made on site. have post method lets them update comment. my normal solution send entire comment model through, let them update it, update on database when post back. involve sending commentid through in hiddenfor , can manipulated. how can verify commentid sent in get method same i'm getting in post , not able alter comment wish? how can verify commentid sent in method same i'm getting in post? basically have validate following things in post - user logged-in user. authenticated users post comments. commentid in post , should valid commentid , should present in database. userid associated logged-in user should same userid associated comment . comment should contain userid column, can check @ time of update. to make sure update happens comment has been sent in get - hold commentid in session , in post action compare commentid value in session .
Read more

node.js - Custom Model Validator SailsJS -

when using custom model validator checks record within model. return within spread doesn't seem end control flow. //post.js model /** * custom validator **/ types: { isuservalid: function(user_id) { var promise = require('bluebird'); promise.all([ user.findone({id: user_id}) ]) .spread(function(user) { console.log(user); if (user === null || user === undefined) { console.log('failed'); return false; }else{ console.log('passed'); return true; } }); } }, my response standard validation failed response. { "error": "e_validation", "status": 400, "summary": "1 attribute invalid", "model": "post", "invalidattributes": { "owner": [ { "rule": "isuservalid", ...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more