excel - Delete top rows in one table based on content from another table -


i beginner in vba.

i have 2 workbooks, 1 containing demand , other storage (or supply).

the demand workbook ordered date - earliest latest.

in storage workbook have quantities of each tool each machine.

i want create subroutine deletes earliest first rows in demand workbook each tool in storage workbook. example, if in storage have 3 tools of type aleris, want delete earliest 3 rows in demand include aleris.

here examples of workbooks:

demand: demand workbook

storage:

storage workbook

here code i've started, i'm stuck. if can advise me ideas on how proceed, or me code i'll glad.

option explicit  sub demand_minus_storage() dim qt integer dim integer  dim demand_wb workbook set demand_wb = workbooks.open("c:\users\rosipov\desktop\eliran\mfg - gss\demand_optics " & format(now(), "dd.mm.yyyy") & ".xlsx")  dim storage_wb workbook set storage_wb = workbooks.open("c:\users\rosipov\desktop\eliran\mfg - gss\opticlabstorage.xlsm")   storage_wb.worksheets("illuminator").range("c3").activate set qt = activecell.value demand_wb.worksheets("illuminators").activate    end sub

this should work against own workbooks, i've left code untouched except integer -> long , commenting out unnecessary lines. (it works fine using test worksheets.)

note uses one loop! inner loop replaced filtering , sorting

sub demand_minus_storage()   'dim qt long   'dim long    dim demand_wb workbook   set demand_wb = workbooks.open("c:\users\rosipov\desktop\eliran\mfg - gss\demand_optics " & format(now(), "dd.mm.yyyy") & ".xlsx")    dim storage_wb workbook   set storage_wb = workbooks.open("c:\users\rosipov\desktop\eliran\mfg - gss\opticlabstorage.xlsm")    'storage_wb.worksheets("illuminator").range("c3").activate   'qt = activecell.value   demand_wb.worksheets("illuminators").activate    dim rngrow range   storage_wb.worksheets("illuminator")     each rngrow in .range(.rows(3), .rows(worksheetfunction.match("*", .range("a:a"), -1))).rows       demand_wb.worksheets("illuminators").usedrange.offset(1)         .sort .columns(5) ' tool type         .offset(-1).autofilter field:=5, criteria1:="=" & rngrow.cells(1) & "*"         .sort .columns(2) ' due date         .specialcells(xlcelltypevisible).entirerow.areas(1)           range(.rows(1), .rows(worksheetfunction.min(rngrow.cells(3), .rows.count))).delete         end         .offset(-1).autofilter         .sort .columns(2) ' due date       end     next   end   cells(1).select  end sub

caveat:

this 1 loop technique only work if tool type in demand table starts name of tool storage table.

i've added tidied , documented version, can understand how works: 

sub demand_minus_storage()    const n_demandheaderrows long = 1   const i_sn_utid   long = 1   const i_due_date  long = 2   const i_tool_type long = 5   const n_storageheaderrows long = 2   const i_tool  long = 1   const i_qt    long = 3    dim rngrow range   dim ƒ worksheetfunction: set ƒ = worksheetfunction    dim storage_wb workbook   set storage_wb = workbooks.open("c:\users\rosipov\desktop\eliran\mfg - gss\opticlabstorage.xlsm")    dim demand_wb workbook   set demand_wb = workbooks.open("c:\users\rosipov\desktop\eliran\mfg - gss\demand_optics " & format(now(), "dd.mm.yyyy") & ".xlsx")    storage_wb.worksheets("illuminator")     ' use worksheet function "match" find last storage used row     ' loop through each storage row     each rngrow in .range(.rows(n_storageheaderrows + 1), .rows(ƒ.match("*", .columns(i_sn_utid), -1))).rows       ' skip header rows , @ same time add @ least 1 row after end of table       demand_wb.worksheets("illuminators").usedrange.offset(n_demandheaderrows)         ' need sort tool type rows deleted contiguous         .sort .columns(i_tool_type)         ' last header row , apply filter         ' filter tool type starts tool in current storage row         .offset(-1).autofilter field:=i_tool_type, criteria1:="=" & rngrow.cells(i_tool) & "*"         ' need re-sort date sorted tool type         .sort .columns(i_due_date)         ' grab first visible contiguous area. there @ least 1 row(s) after end of table.         ' if there matching tool tips, these form area preceding end of table area.         .specialcells(xlcelltypevisible).entirerow.areas(1)           ' make sure don't delete more rows found.           ' if none found, empty rows @ end of table deleted.           range(.rows(1), .rows(ƒ.min(rngrow.cells(i_qt), .rows.count))).delete         end         ' turn autofilter off , show hidden rows         .offset(-n_demandheaderrows).autofilter         ' need re-sort date hidden rows not sorted in previous date sort         .sort .columns(i_due_date)       end     next   end   ' tidy   cells(1).select  end sub

Comments

Post a Comment

Popular posts from this blog

Sort a complex associative array in PHP -

this question has answer here: php sort array subarray value 5 answers i have associative array in php like: $newarray1 = array ( [producta] => array ( [link] => http://sanjosespartan.com/blog/products/agile-java-development/ [visitcount] => 4 ) [productd] => array ( [link] => http://sanjosespartan.com/blog/products/intro-to-go/ [visitcount] => 1 ) [productg] => array ( [link] => http://sanjosespartan.com/blog/products/node-js-for-developers/ [visitcount] => 1 ) [productb] => array ( [link] => http://sanjosespartan.com/blog/products/beginning-mysql/ [visitcount] => 1 ) [productc] => array ( [link] => http://sanjosespartan.com/blog/products/computer-networks/ [visitcount] => 1 ) ...
Read more

vb.net - How to ignore if a cell is empty nothing -

how use roscolor() ignore value (like empty cell) ? color row if value upper 5 when there nothing in cell, want ignore roscolor() apply, how? public sub roscolor() integer = 0 quotedatagridview1.rows.count() - 1 step +1 dim val integer val = quotedatagridview1.rows(i).cells(3).value if val = vbempty quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.white elseif val < 5 quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.red elseif val > 5 , val < 10 quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.lightyellow end if next end sub you can check empty value following public sub roscolor() integer = 0 quotedatagridview1.rows.count() - 1 step +1 dim val = quotedatagridview1.rows(i).cells(3).value if isdbnull(val) or val nothing quotedatagridview1.rows(i).defaultcellstyle.backcolor = color.white ...
Read more

recursion - Can every recursive algorithm be improved with dynamic programming? -

i first year undergraduate csc student looking competitive programming. recursion involves defining , solving sub problems. understand, top down dynamic programming (dp) involves memoizing solutions sub problems reduce time complexity of algorithm. can top down dp used improve efficiency of every recursive algorithm overlapping sub problems? dp fail work , how can identify this? the short answer is: yes. however, there constraints. obvious 1 recursive calls must overlap. i.e. during execution of algorithm, recursive function must called multiple times same parameters. lets truncate recursion tree memoization. can use memoization reduce number of calls. however, reduction of calls comes price. need store results somewhere. next obvious constraint need have enough memory. comes not-so obvious constraint. memory access requires time. first need find result stored , maybe copy location. in cases, might faster let recursion calculate result instead of loading somewher...
Read more