excel - How to copy paste row inside a loop if a cell is abiding by an if condition vba -


i built macro copy paste rows contain cells abide if condition. macro measures difference of values between 4 pairs of cells of row and, if difference bigger desired one, copy-pastes row, cell containing ‘guilty’ value (or values if 4 comparisons not abiding restrictions) belongs (or they), in different sheet (“wfrandvfr_performance”). @ end, colors "guilty" cells. works fine apart paste part, provided below:

    sheets("wfrandvfr_performance").select     range("a" & rows.count).end(xlup).offset(1).select     activesheet.paste

below posting macro

 sub wfrandvfr_performance()          application.calculation = xlcalculationmanual         application.screenupdating = false         application.enableevents = false      sheets("tracker").select          dim mdiff1 double         mdiff1 = 0.01         dim mdiff2 double         mdiff2 = 0.03         dim mdiff3 double         mdiff3 = 0.01         dim mdiff4 double         mdiff4 = 0.03          sheets("tracker").select         each cell1 in range(range("u2"), range("u2").end(xldown))         if cell1.value - cell1.offset(0, 1).value > mdiff1 or cell1.value - cell1.offset(0, 2).value > mdiff2         cell1.entirerow.copy         sheets("wfrandvfr_performance").select         range("a" & rows.count).end(xlup).offset(1).select         activesheet.paste         end if         next cell1          sheets("tracker").select         each cell2 in range(range("ab2"), range("ab2").end(xldown))         if cell2.value - cell2.offset(0, 1).value > mdiff3 or cell2.value - cell2.offset(0, 2).value > mdiff4         cell2.entirerow.copy         sheets("wfrandvfr_performance").select         range("a" & rows.count).end(xlup).offset(1).select         activesheet.paste         end if         next cell2          sheets("wfrandvfr_performance").select          columns(4).removeduplicates columns:=array(1)         on error resume next         columns(4).specialcells(xlblanks).entirerow.delete          each cell3 in range(range("u2"), range("u2").end(xldown))         if cell3.value - cell3.offset(0, 1).value > mdiff1         cell3.offset(0, 1).interior.colorindex = 3         end if         if cell3.value - cell3.offset(0, 2).value > mdiff2         cell3.offset(0, 2).interior.colorindex = 5         end if         next cell3          each cell4 in range(range("ab2"), range("ab2").end(xldown))         if cell4.value - cell4.offset(0, 1).value > mdiff3         cell4.offset(0, 1).interior.colorindex = 3         end if         if cell4.value - cell4.offset(0, 2).value > mdiff4         cell4.offset(0, 2).interior.colorindex = 5         end if         next cell4          sheets("wfrandvfr_performance").select         if not activesheet.autofiltermode         activesheet.rows(1).autofilter         end if      application.calculation = xlcalculationautomatic     application.screenupdating = true     application.enableevents = true      end sub

you find last row such:

dim lr long lr = sheets("wfrandvfr_performance").cells(sheets("wfrandvfr_performance").rows.count, 1).end(xlup).row  cell1.entirerow.copy sheets("wfrandvfr_performance").range("a" & lr+1)

another option, , best (avoids copy/paste):

dim lr long lr = sheets("wfrandvfr_performance").cells(sheets("wfrandvfr_performance").rows.count, 1).end(xlup).row  sheets("wfrandvfr_performance").range("a" & lr+1).value=cell1.entirerow.value

putting code:

dim lr long  sheets("wfrandvfr_performance").rows(1).value=sheets("tracker").rows(1).value      each cell1 in range(range("u2"), range("u2").end(xldown))         if cell1.value - cell1.offset(0, 1).value > mdiff1 or cell1.value - cell1.offset(0, 2).value > mdiff2             lr = sheets("wfrandvfr_performance").cells(sheets("wfrandvfr_performance").rows.count, 2).end(xlup).row             sheets("wfrandvfr_performance").range("a" & lr+1).value=cell1.entirerow.value         end if     next cell1

Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer
Read more

jquery - Responsive Navbar with Sub Navbar -

good day, forgive me nav vocabulary not best. if question confusing apologize. i'm working on navigation bar needs centered on page @ 768px , above. items in nav open (or perhaps toggle) subnav lives directly below. these nav should appear centered on page. i have been able work through responsive portion of nav bar, having main items align vertically , sub nav menu of each item roll out below, , gave them indent separation. working well. however, issue not responsive side, behavior @ width greater 768px. happens toggling menu's sub navigation pushes other main items down page below sub nav. here's code: $(document).ready(function () { //stick in fixed 100% height behind navbar don't wrap $('#slide-nav.navbar-inverse').after($('<div class="inverse" id="navbar-height-col"></div>')); $('#slide-nav.navbar-default').after($('<div id="navbar-height-col"></d
Read more