EXCEL VBA: Compare then Update/change, remove and add between 2 sheets -

-

i'm comparing 2 worksheets in same workbook, row row (and each cell of row) code able identify of row has been changed(change), if doesn't exist in second sheet show removed (remove), or if exist in second sheet needs added (add). tab in work sheet are:

original \ updated \ changes

what trying achieve create fourth 1 (final) changes applied, before there found problems code (btw source , template found at: here)it works great(with remove , add), when using great number of registries (hundreds) of them, flagged changes doesn't display right values, , sometimes, reworking in same tabs , trying apply the macro again gets error @ marked line(*).

i.e.: original \ updated \ changed

car_01 |500| ms \ car_01 |750 |ms \ car_01| 15.5| ms

at first approach problem thinking related type of parameter @ cells vs input has in macro far haven't found right type (already have try it: general, number , text). scope in solutions of how display fourth sheet , problem values types appreciate it.

sub comparesheets() application.screenupdating = false  ' constants '  worksheets & ranges '   original const kswsoriginal = "original" const ksoriginal = "originaltable" const ksoriginalkey = "originalkey" '   updated const kswsupdated = "updated" const ksupdated = "updatedtable" const ksupdatedkey = "updatedkey" '   changes const kswschanges = "changes" const kschanges = "changestable" '  labels const kschange = "change" const ksremove = "remove" const ksadd = "add" ' ' declarations dim rngo range, rngok range, rngu range, rnguk range, rngc range dim c range dim long, j long, lchanges long, lrow long, bequal boolean ' ' start set rngo = worksheets(kswsoriginal).range(ksoriginal) '(*)here gets marked error of debugger set rngok = worksheets(kswsoriginal).range(ksoriginalkey) set rngu = worksheets(kswsupdated).range(ksupdated) set rnguk = worksheets(kswsupdated).range(ksupdatedkey) set rngc = worksheets(kswschanges).range(kschanges) rngc     if .rows.count > 1         range(.rows(2), .rows(.rows.count)).clearcontents         range(.rows(2), .rows(.rows.count)).font.colorindex = xlcolorindexautomatic         range(.rows(2), .rows(.rows.count)).font.bold = false     end if end ' ' process lchanges = 1 '  1st pass: updates & deletions rngok     = 1 .rows.count         set c = rnguk.find(.cells(i, 1).value, , xlvalues, xlwhole)         if c nothing             ' deletion             lchanges = lchanges + 1             rngc.cells(lchanges, 1).value = ksremove             j = 1 rngo.columns.count                 rngc.cells(lchanges, j + 1).value = rngo.cells(i, j).value                 rngc.cells(lchanges, j + 1).font.color = vbred                 rngc.cells(lchanges, j + 1).font.bold = true             next j         else             bequal = true             lrow = c.row - rnguk.row + 1             j = 1 rngo.columns.count                 if rngo.cells(i, j).value <> rngu.cells(lrow, j).value                     bequal = false                     exit                 end if             next j             if not bequal                 ' change                 lchanges = lchanges + 1                 rngc.cells(lchanges, 1).value = kschange                 j = 1 rngo.columns.count                     if rngo.cells(i, j).value = rngu.cells(lrow, j).value                         rngc.cells(lchanges, j + 1).value = rngo.cells(i, j).value                     else                         rngc.cells(lchanges, j + 1).value = rngu.cells(i, j).value                         rngc.cells(lchanges, j + 1).font.color = vbmagenta                         rngc.cells(lchanges, j + 1).font.bold = true                     end if                 next j             end if         end if     next end '  2nd pass: additions rnguk     = 1 .rows.count         set c = rngok.find(.cells(i, 1).value, , xlvalues, xlwhole)         if c nothing             ' addition             lchanges = lchanges + 1             rngc.cells(lchanges, 1).value = ksadd             j = 1 rngu.columns.count                 rngc.cells(lchanges, j + 1).value = rngu.cells(i, j).value                 rngc.cells(lchanges, j + 1).font.color = vbblue                 rngc.cells(lchanges, j + 1).font.bold = true             next j         end if     next end ' ' end worksheets(kswschanges).activate rngc.cells(2, 3).select set rngc = nothing set rnguk = nothing set rngu = nothing set rngok = nothing set rngo = nothing beep ' application.screenupdating = true end sub

as additional note, test different methods apply solution (lookup, ...) far best approach.

i have located error of change status, , related absolute reference in loop, instance: original tab has value in row 505 parameter car_red has value of 23 updated tab has same parameter (car_red) in row 575 value 27 code notice difference, instead of copy new value, value updated tab in row 505 (as original tab location of value), guess somehow need variable catch new value of parameter use reference updated tab.

there error in extract below first listing. 

rngc.cells(lchanges, j + 1).value = rngu.cells(i, j).value

should 

rngc.cells(lchanges, j + 1).value = rngu.cells(lrow, j).value

as i refers row position in original file while lrow refers matching entry row position in update file.

for j = 1 rngo.columns.count    if rngo.cells(i, j).value = rngu.cells(lrow, j).value       rngc.cells(lchanges, j + 1).value = rngo.cells(i, j).value    else       rngc.cells(lchanges, j + 1).value = rngu.cells(i, j).value       rngc.cells(lchanges, j + 1).font.color = vbmagenta       rngc.cells(lchanges, j + 1).font.bold = true    end if next j

Comments

Post a Comment

Popular posts from this blog

python - mat is not a numerical tuple : openCV error -

-
i have write down code showing error ma not getting it: please help: showing mat not numerical tuple: import cv import cv2 capture = cv2.videocapture("j.3gp") while(1): _, frame1 = capture.read() grayimage1 = cv2.cvtcolor(frame1, cv2.color_bgr2gray) _, frame2 = capture.read() grayimage2 = cv2.cvtcolor(frame2, cv2.color_bgr2gray) differenceimage = cv2.absdiff(grayimage1,grayimage2) thresholdimage = cv2.threshold(differenceimage,25,255,cv2.thresh_binary) cv2.imshow("difference image", differenceimage) cv2.imshow("threshold image", thresholdimage) cv2.imshow("image", frame1) k = cv2.waitkey(30) & 0xff error arising : ----------------------------------------------------------------------------------------- traceback (most recent call last): file "desk.py", line 15, in <module> cv2.imshow("threshold image", thresholdimage) typeerror: mat not numerical tuple ...
Read more

c# - MSAA finds controls UI Automation doesn't -

-
i'm working on automating windows application. i'm using teststack white framework. i've hit problem. program has 'window' object cannot see inside of. white shows no controls inside of it. inspect.exe shows no controls inside of either when running in ui automation mode. if switch inspect msaa see controls inside fine. there anyway me use msaa c# handle on these controls? if can identify msaa functions need, can use p/invoke call them c#. here's example article doing msaa: http://www.codeproject.com/articles/38906/ui-automation-using-microsoft-active-accessibility also, pinvoke.net can used identify iaccessible (msaa) functions: http://www.pinvoke.net/search.aspx?search=iaccessible&namespace=[all] here's answer along lines: msaa com-based? finally, alternative p/invoke, might able use tlbimp.exe create wrapper assembly oleacc.dll, , access msaa functions through it. i'm not sure if works msaa, it's worth try. as e...
Read more

wordpress - .htaccess: RewriteRule: bad flag delimiters -

-
pulling hair out on one. have following .htaccess file custom redirects in addition wordpress's default settings. when tested on local wamp server worked fine after moving production i'm not getting rewriterule: bad flag delimiters error in server log , after while site goes down internal server error any appreciated. in advance! <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^rockford_weddings___welcome\.html$ /wedding/ [r=301,l] rewriterule ^blog/?$ /journal/ [r=301,l] rewriterule ^blog/(.*)$ /$1 [r=301,l] rewriterule ^portraitinvestment/?$ /portraitinvestment\.pdf [r=301,l] rewriterule ^weddinginvestment/?$ /weddinginvestment\.pdf [r=301,l] rewriterule ^holidaycards2014/?$ /holidaycards2014\.pdf [r=301,l] </ifmodule> # begin wordpress <ifmodule mod_rewrite.c> rewriteengine on rewritebase / rewriterule ^index\.php$ - [l] rewritecond %{request_filename} !-f rewritecond %{request_filename} !-d rewriterule . /index.php [l] </...
Read more