excel - Read a value from spreadsheet X, compare adjacent values between spreadsheets X and Y -

i have macro based excel file generates list of items received , status (i.e. received, repaired, etc). program runs daily, , right have capture previous day's list , place in spreadsheet called previousdata before updating current day's list, placed in spreadsheet called data; used compare believe fixed/changed status on previous day.

i'm self taught in vba, i'm not super efficient or experienced. want following:

1. on data spreadsheet, grab order number starting in j2

2. switch previousdata spreadsheet, , search order number step 1

scenario a: if order number found on previousdata, compare status values next order number on both sheets; if differ, run code otherwise nothing

scenario b: if order number not found on previousdata, nothing

1. repeat until 1st blank cell encountered in data spreadsheet

i did searching around interwebs , found (it might have been forum, actually) go row row , compare cell values, if scenario b came function fail "out of range." here code tried , have modified try work:

sub output()  dim varsheeta variant dim varsheetb variant dim varsheetrma variant dim strrangetocheck string dim strrangerma string dim variable string dim irow long dim icol long dim count integer  strrangetocheck = "k2:l1000" strrangerma = "j2:j1000" ' if know data in smaller range, reduce size of ranges above. debug.print varsheeta = worksheets("previousdata").range(strrangetocheck) varsheetb = worksheets("data").range(strrangetocheck) ' or whatever other sheet is. varsheetrma = worksheets("data").range(strrangerma) debug.print  sheets("data").select range("j2").select selection.copy sheets("previousdata").select cells.find(what:=variable, after:=activecell, lookin:=xlformulas, _     lookat:=xlpart, searchorder:=xlbyrows, searchdirection:=xlnext, _     matchcase:=false, searchformat:=false).activate  irow = lbound(varsheeta, 1) ubound(varsheeta, 1)     icol = lbound(varsheeta, 2) ubound(varsheeta, 2)         if varsheeta(irow, icol) = varsheetb(irow, icol)             ' cells identical.             ' nothing.         else             ' cells different.             ' code goes here whatever want do.         end if     next icol next irow  end sub 

please :)

this code should easier understand + job.

option explicit  sub comparestatuses()   dim ws1 worksheet, ws2 worksheet, rng1 range, rng2 range  dim lr1&, lr2&, i&, j&   set ws1 = thisworkbook.sheets("data")  set ws2 = thisworkbook.sheets("previousdata")  lr1 = ws1.range("j" & rows.count).end(xlup).row  lr2 = ws2.range("j" & rows.count).end(xlup).row   = 2 lr1   j = 2 lr2      set rng1 = ws1.range("j" & i)      set rng2 = ws2.range("j" & j)      if strcomp(cstr(rng1.value), cstr(rng2.value), vbtextcompare) = 0 , _         strcomp(cstr(rng1.offset(0, 1).value), cstr(rng2.offset(0, 1).value) _          ,vbtextcompare) <> 0          ' found matching order + both statuses different          ' wanted run code      end if      set rng1 = nothing      set rng2 = nothing   next j  next end sub