excel - Compare two separate documents VBA -

so want have macro in workbook opens workbook b , c. after goes through column of workbooks b , c , 2 values equal takes value workbook c , pastes column of workbook a.

i have written below code, think it's easier way please feel free write own one. thank , please me :)

sub reportcomparealta() ' ' reportcomparealta macro ' adds instances column d "alta"      dim varsheeta variant     dim varsheetb variant     dim varsheetc variant     dim strvalue variant     dim strrangetocheck string     dim irow long     dim icol long     dim wbka workbook     dim wbkb workbook     dim wbkc workbook     dim counter long      set wbka = workbooks.open(filename:="g:\reporting\ah_misse_feb2013.xls")     set wbkb = workbooks.open(filename:="g:\reporting\ah_misse_mar2013.xls")     set wbkc = workbooks.open(filename:="g:\reporting\reportcompare.xls")     set varsheeta = wbka.worksheets("localesmallcontratos")     set varsheetb = wbkb.worksheets("localesmallcontratos")     set varsheetc = wbkc.worksheets("sheet1")       strrangetocheck = "a1:iv65536"      debug.print     varsheeta = wbkc.worksheets("sheet2").range(strrangetocheck) 'may confusing code here     varsheetb = wbkc.worksheets("sheet3").range(strrangetocheck) 'may confusing code here     debug.print      counter = 0      irow = lbound(varsheeta, 1) ubound(varsheeta, 1)                     if varsheetb(irow, "b") = varsheeta(irow, "b") & varsheetb(irow, "b") <> "gerencia" & varsheetb(irow, "b").value <> "" & varsheetb(irow, "d") = "alta"                 strvalue = ""                 varsheetb.range("irow:b").select                  selection = strvalue                 activesheet = varsheetc                 range("a1").select                 selection.offset(counter, 0).value = strvalue                 counter = counter - 1              else                 msgbox ("done")             end if              next irow  end sub 

i see obvious errors mat richardson points out, using & not equivalent shorthand and operator, it's concatenator, not want when say:

if varsheetb(irow, "b") = varsheeta(irow, "b") & varsheetb(irow, "b") <> "gerencia" & varsheetb(irow, "b").value <> "" & varsheetb(irow, "d") = "alta" then

which brings me error:

varsheetb (and a, , c matter) variant/array variables. cannot index these irow, "b" because cannot use non-numeric index. perhaps mean (irow, 2).

on related note: varsheetb.range("irow:b").select fail, because cannot .select variant. not range variable. further, irow:b not correct either of variant array or range variable. also, @ point, varsheetb no longer worksheet object variable.

which brings me biggest error: using variables varsheeta, varsheetb, , varsheetc represent (at different times in code) both worksheet object , variant array of values. confusing, , causing errors described above. variable cannot both of these things @ same time, need treating variants variants when variants, , worksheets when worksheets, or better yet: use worksheet variables worksheets , variants arrays, don't use same variable multiple purposes.

sub reportcomparealta() ' ' reportcomparealta macro  dim varsheeta worksheet dim varsheetb worksheet dim varsheetc worksheet dim rangetocheck range dim cl range dim irow long dim icol long dim wbka workbook dim wbkb workbook dim wbkc workbook dim counter long  set wbka = workbooks.open(filename:="g:\reporting\ah_misse_feb2013.xls") set wbkb = workbooks.open(filename:="g:\reporting\ah_misse_mar2013.xls") set wbkc = workbooks.open(filename:="g:\reporting\reportcompare.xls") set varsheeta = wbka.worksheets("localesmallcontratos") set varsheetb = wbkb.worksheets("localesmallcontratos") set varsheetc = wbkc.worksheets("sheet1")   set rangetocheck = varsheeta.range("a1:a65536") '## change because indicate want compare column ##'  counter = 0  '## loop on cells in range. ##' '## not efficient, easiest ##' each cl in rangetocheck        '## comparison here, e.g: ##'     '## ignore cells .offset(0,3).value = "alta" or cl.value = "" ##'     if not cl.offset(0,3).value = "alta" or not cl.value = vbnullstring         if not cl.value = varsheetb.range(cl.address).value            '## values not equal, something:                 varsheetc.range(cl.address) = "not equal"            counter = counter+1         else:            '## values equal, else:             varsheetc.range(cl.address) = "equal"         end if    end if next   msgbox "done! there " & counter & " mismatch values", vbinformation end sub