A Macro nedded

[hugh999]

I have a large list of names in column (A) OF an MS Excel spreadsheet and a numbers in column (B)that are associated with the names. What i wish to do is change the number assoiacted with at least 320 of those names, to do this manually will take hours, so is their a piece of code i could use to create a macro that would allow me to search for the names in Column (A)and change the numbers associated with those names.

I would appreciate your help with this, as i am new to macros and have been trying to create a macro for this task, but with no luck.

 

[dsi]

If you want to change the numbers based a known (new) list, you can use vlookup. Just create a new table with the correct information and put the vlookup formula in place of the number on the large table. Then, if you want to remove the vlookup formula, just copy the results and 'paste special' to extract the new values.

 

[cheerio]

This rather depends on the form of your new data.

If it is on another sheet in a similar form to the original data then the update process itself can be automated using a macro.

If you have 320 emails providing new figures to be applied your problem is quickly positioning yourself on the right row. If row 1 of your worksheet has column headings with the data under those headings look at the command Data,Filter,AutoFilter. This will turn the headings into dropdown boxes so you can choose the names. However, this is not practical for more than about 500 names.

Any automated solution should cope with the possibility that names in the new list do not exist in the original (eg because of typos).

Without really knowing your starting point I cannot be more helpful.

 

[hugh999]

List (A) contains a list of 500 names and list (B)contains membership fee that each of the people have paid. I have extracted alist of 320 names out of the 500 names and wish to chanege their membership fee too a set figure ($20).

I have copied these 320 names to column (C) and would like the code to start at the first name in column (C), search column (A) for the name and set the value in column (B) to $20 or 20, then look at the second name in column (C), search column (A) for the name and set it to the same value and so on untill it has finished looking at all the names in column (C)

Thanks

 

[sfvb]

Since this looks like a one time deal, you could just enter:
=IF(ISNA(MATCH(A1,C$1:C$320,0)),B1,20)
into cell D1. Then copy D1 and paste into D2500.

This will give the correct membership fee in column D. Then just copy column D and do a Paste Special "Values" back into column B.