Update E-Mail Data Base

[Brian Matlack]

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 (Alpha-numeric) column B customer address (I want it to be an active link blue underlined).

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

Thanks for any help or direction!

[Tom Ogilvy]

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
>
>

[Brian Matlack]

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

[Tom Ogilvy]

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
>
>

[Brian Matlack]

Thanks Tom!! Works like a charm!!

Have a great Day!
Brian