Excel VBA - Checking if value of each cell in one range is in another range -
i trying have workbook current work ticket data updated new ticket data. have nested each loops @ bottom check equality in ticket number. if finds match, supposed update cells new data. if ticket not in list of tickets, it's supposed add new ticket bottom. keeps happening tickets in spreadsheet, keeps adding tickets newdata bottom of currentdata. think problem lies logic in these nested loops can't figure out i'm doing wrong.
sub getnewdata() dim newdata workbook dim ndlastrow long dim currentdata workbook dim cdlastrow long dim ndrangetocheck range dim cdrangetocheck range dim ndrow long dim cdrow long set newdata = workbooks.open("c:\users\<user>\documents\newdata.xlsx") set currentdata = thisworkbook ' assign last row , range compare each workbook newdata.worksheets("incident list").range("a2").select activesheet ndlastrow = .cells(.rows.count, "a").end(xlup).row end set ndrangetocheck = newdata.worksheets("incident list").range("a2", cells(ndlastrow, "a")) currentdata.worksheets("incident list").activate activesheet cdlastrow = .cells(.rows.count, "b").end(xlup).row end set cdrangetocheck = currentdata.worksheets("incident list").range("b2", cells(cdlastrow, "b")) ' iterate through compare incident #s between workbooks dim rout range dim rin range dim match boolean each rout in ndrangetocheck.cells match = false each rin in cdrangetocheck.cells if cells(rin.row, rin.column).value = cells(rout.row, rout.column).value match = true ndrow = rout.row cdrow = rin.row currentdata.worksheets("incident list").cells(cdrow, "l").value = newdata.worksheets("incident list").cells(ndrow, "d").value currentdata.worksheets("incident list").cells(cdrow, "o").value = newdata.worksheets("incident list").cells(ndrow, "f").value currentdata.worksheets("incident list").cells(cdrow, "p").value = newdata.worksheets("incident list").cells(ndrow, "g").value currentdata.worksheets("incident list").cells(cdrow, "q").value = newdata.worksheets("incident list").cells(ndrow, "h").value currentdata.worksheets("incident list").cells(cdrow, "s").value = newdata.worksheets("incident list").cells(ndrow, "l").value currentdata.worksheets("incident list").cells(cdrow, "t").value = newdata.worksheets("incident list").cells(ndrow, "n").value currentdata.worksheets("incident list").rows(rin.row).borders.linestyle = xlcontinuous exit end if next rin if match = false ndrow = rout.row currentdata.worksheets("incident list").cells(cdlastrow, "b").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "a").value currentdata.worksheets("incident list").cells(cdlastrow, "b").offset(1, 0).numberformat = "0" currentdata.worksheets("incident list").cells(cdlastrow, "l").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "d").value currentdata.worksheets("incident list").cells(cdlastrow, "o").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "f").value currentdata.worksheets("incident list").cells(cdlastrow, "p").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "g").value currentdata.worksheets("incident list").cells(cdlastrow, "q").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "h").value currentdata.worksheets("incident list").cells(cdlastrow, "s").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "l").value currentdata.worksheets("incident list").cells(cdlastrow, "t").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "n").value currentdata.worksheets("incident list").cells(cdlastrow, "f").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "c").value currentdata.worksheets("incident list").cells(cdlastrow, "m").offset(1, 0).value = newdata.worksheets("incident list").cells(ndrow, "e").value currentdata.worksheets("incident list").cells(cdlastrow, "m").offset(1, 0).numberformat = "m/d/yyyy" currentdata.worksheets("incident list").rows(cdlastrow).offset(1, 0).borders.linestyle = xlcontinuous ' reset cdlastrow currentdata.worksheets("incident list").activate activesheet cdlastrow = .cells(.rows.count, "b").end(xlup).row end end if next rout newdata.close end sub
i think fault in following line:
if cells(rin.row, rin.column).value = cells(rout.row, rout.column).value where both cells references point currentdata while guess correct 1st cells occurrence while 2nd 1 should point newdata
this due use of select/selection , or activate/activeyyy methods/objects quote prone loose control on actual referenced workbook/worksheet
you should use qualified range references parent workbook
other can take advantage of using find() method avoid loops , ... little tricks shorten code down bit, follows:
option explicit sub getnewdata() dim newdata worksheet, currentdata worksheet '<--| use worksheets since need work dim ndrangetocheck range, cdrangetocheck range '<-- data ranges dim rout range, f range '<--| helper ranges dim cdrow long dim cdaddressstrng string, ndaddressstrng string workbooks.open "c:\users\<user>\documents\newdata.xlsx" set newdata = activeworkbook.worksheets("incident list") '<--| set sheet since need work set currentdata = thisworkbook.worksheets("incident list") '<--| set sheet since need work ' assign last row , range compare each workbook newdata set ndrangetocheck = .range("a2", .cells(.rows.count, "a").end(xlup)) end currentdata set cdrangetocheck = .range("b2", .cells(.rows.count, "b").end(xlup)) end cdaddressstrng = "l|, o|, p|, q|, s|, t|" ndaddressstrng = "d|, f|, g|, h|, l|, n|" ' iterate through compare incident #s between workbooks each rout in ndrangetocheck set f = cdrangetocheck.find(what:=rout.value, lookat:=xlwhole, lookin:=xlvalues, matchcase:=false) '<--| "new" value current data if f nothing '<--| if not found... cdrow = currentdata.cells(currentdata.rows.count, "b").end(xlup).row + 1 '<--| ...retrieve current data first empty row else '<--| otherwise... cdrow = f.row '<--| ......retrieve found cell row index end if currentdata.range(replace(cdaddressstrng, "|", cdrow)).value = newdata.range(replace(ndaddressstrng, "|", rout.row)).value currentdata.rows(cdrow).borders.linestyle = xlcontinuous next rout newdata.parent.close '<--| close newdata workbook: since newdata worksheet need "climb up" parent workbook end sub
Comments
Post a Comment