Once I autofilter a certain column, how do I then populate all cells in another column that have been filtered using vba? For example, column A is populated with "name1," "name2" and "name 3" randomly in cells A2:A100. I want to filter by "name1" and populate all cells in column B with "A," then filter by "name2" and populate the filtered data with "B" in column B and then the filter by "name3" and populate "C" in column B. I know how to autofilter in vba, but am not sure how to populate only those cells in column B that have been autofiltered. Any help would be much appreciated.

11/19/2006 12:29:49 PM

why don't you just use an IF function or a VLOOKUP in column B to fill in data based on column A, instead of autofiltering and adding data.

e.g. from your simplified example

lookup table in cells C1: D3 -

name1   A
name2   B
name3   C

11/19/2006 2:22:00 PM

Sorry, I should have included more info. The s/s already has a vlookup for the name column that returns the name based on the client (i.e. each person is assigned a client). The problem lies in that two people now cover the same client, but for different products (ie. John covers company A, product1 while Sara covers Product2 for Company A). Before, all we had to do was vlookup the client, but now we have to take into account the product as well. I figured if I could assign all products from Comany A to John via a vlookup, then filter by Product2 and populate the name column with Sara. Any ideas?

Name Product Client
John Product1 Company A
Sara Product2 Company A
Bill Product1 Comany B
Bill Product2 Company B

[Edited on November 19, 2006 at 4:43 PM. Reason : .]

11/19/2006 4:42:54 PM

well, almost all lookup problems can be solved with formulas instead of VBA.
i'll come back to the problem in a bit and let you know

11/19/2006 4:44:26 PM

hey.
sorry, i'm just trying to wrap my head around this. I'm going in circles here as to what the given data is and what is calculated.
It doesn't help that, by convention, the caculated (dependant) data is generally on the right, and the given (independant) data is generally on the left, but in your table and from your description, it sounds like you're given a Client and product, and you're solving for the Name.

what are the conventions? which of these statements are true:
1 or more Names (salesmen) can cover any client
A Client can have 1 or more Products
Only 1 Salesman can cover one product for one company
The same product (1, 2) can be covered by multiple salesmen, given it's for different Clients

..... from the looks of your sample data, the assignments are kind of arbitrary, and therefore cannot be looked-up in a systematic way.

11/19/2006 5:44:23 PM

The data is actually in the order of Client, Product then Name. We have a report that generates all current clients and what products they bought. We then vlookup the name column against a separate spreadsheet that contains client names and salesmen names in order to populate the report's name column with the appropriate salesmen. Recently, we have assigned coverage of a rather large client based on product, so John now covers only Product1 for Company A and Sara covers Product2 for Comany A. All other clients are covered, irrespective of products, by one salesmen (i.e. Bill covers company B for all products and Tim covers company C for all product.) The reason I mentioned vba is we also add several more columns of calculations to the raw data and compile everything in pivot tables via a macro.

11/20/2006 6:16:54 AM