Crosscheck ranges

[Steve]

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

[Tom Ogilvy]

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 news:446cff97$1@dnews.tpgi.com.au...
> 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
>
>

[Steve]

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

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:evyXdAueGHA.3888@TK2MSFTNGP02.phx.gbl...
> 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 news:446cff97$1@dnews.tpgi.com.au...
>> 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
>>
>>
>
>