Crosscheck ranges
 

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

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

Crosscheck ranges
 

Tom
Thank you. Works well with very slight mod.
Steve

"Tom Ogilvy" wrote in message
...
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