View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Crosscheck ranges

Dim rng1 as Range, rng2 as Range
Dim v1 as Variant, rw as Long, i as Long
With Workbooks("Book1.xls").Worksheets(1)
set rng1 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End With
With Workbooks("Book2.xls").worksheets(1)
set rng2 = .Range(.Cells(1,1),.Cells(1,1).End(xldown))
End with
rw = rng2.Rows(rng2.Rows.count).Row + 1

v1 = rng1.Value
for i = lbound(v1,1) to ubound(v1,1)
res = application.Match(v1(i,1),rng2,0)
if iserror(res) then
rng2.parent.Cells(rw,1).Value = v1(i,1)
rw = rw + 1
end if
Next

--
regards,
Tom Ogilvy



"Steve" <No Spam wrote in message ...
I have two ranges in two sheets within the same workbook that need to be
cross-checked after automatic data replacement. If a value is not found in
range 1 it needs to be appended to range 2. I looked at looping (While ...
< "") but it seems a tad slow. Can soomeone recommend a fast method?

--
Steve