Hi!
I need to update a list of e-mail addresses several hundred rows long
I get a report that varies in size and may contain a change of addres
for some customers or it may be a new address for a customer that I di
not have an address for. I want to use (list "upd") to update (lis
"db"). Both lists are constructed the same way, Column A custome
number column B customer address.

Is there a code that will search "upd" and then change or add info i
"db"?

Thanks for any help or direction

--
Brian Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=54944

Are you using the generic term list or are these Listobjects in xl2003?

--
Regards,
Tom Ogilvy


"Brian Matlack" wrote:


Hi!
I need to update a list of e-mail addresses several hundred rows long.
I get a report that varies in size and may contain a change of address
for some customers or it may be a new address for a customer that I did
not have an address for. I want to use (list "upd") to update (list
"db"). Both lists are constructed the same way, Column A customer
number column B customer address.

Is there a code that will search "upd" and then change or add info in
"db"?

Thanks for any help or direction!


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=549446

Hi Tom!
Sorry for the confusion I am using the generic term not the "Exce
Lists".
I believe that I will be using named ranges ultimately.

Bria

--
Brian Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=54944

Assumes both lists are in the same workbook and as you said, the "upd" and
"db" are named ranges with the first column of each containing the unique
identifier which can be used to match between the databases. If there are
more than 10 columns involved, then change the 10 to reflect the number of
columns (minus the first)

Test this on a copy of your data and probably always make a backup of your
data before you run it.


Sub UpdateList()
Dim rngu as Range, rngdb as Range
Dim cell as Range, rng1 as Range

set rngu = range("upd).Columns(1).Cells
set rngdb = range("db").columns(1).Cells
for each cell in rngu
res = application.Match(cell.Value,rngdb,0)
if not iserror(res) then
set rng1 = rngdb(res)
cell.offset(0,1).Resize(1,10).copy _
rng1.offset(0,1)
end if
Next
End sub

--
Regards,
Tom Ogilvy

"Brian Matlack" wrote:


Hi Tom!
Sorry for the confusion I am using the generic term not the "Excel
Lists".
I believe that I will be using named ranges ultimately.

Brian


--
Brian Matlack
------------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...fo&userid=3508
View this thread: http://www.excelforum.com/showthread...hreadid=549446

Thanks Tom!! Works like a charm!!

Have a great Day!
Bria

--
Brian Matlac
-----------------------------------------------------------------------
Brian Matlack's Profile: http://www.excelforum.com/member.php...nfo&userid=350
View this thread: http://www.excelforum.com/showthread.php?threadid=54944