I have a Parts spreadsheet that I want to be able to fill in the first column in a row, which is the part number, and then it automatically fills in the other columns in the row, which are connector, speed, size, manufacturer, etc. Should I use a macro for this? Should I create a VBA class module for each different type of part, hds, RAM, motherboards, etc?

Hi, You should be able to do this with standard formulas, with no need for macros.

The VLOOKUP() function allows you to lookup a value (the lookup value) in a table and return a matching item.

If your table has the part number in the left most column, then the various elements such as connector, speed, size, manufacturer etc. in subsequent columns, VLOOKUP will find the part number and return values from any column to the right in the table.

As an example, if you have your table on Sheet2 with part number in column A (say 50 parts, so A1 to A50), with connector in column B (B1 to B50), and so on with say cost as the last element in Column G (G1 to G50),

then enter a part number in cell A1, and this formula in cell A2 will return the cost:

=VLOOKUP(A1,Sheet2!$A$1:$G$50,7,FALSE)The 7 in the formula is because the result, in this case cost, is in column G which is the seventh column in the table

If the part number is not present it returns #NA, but this can be dealt with by a modification of the formula. See this :

recent post.Regards

BTW for most macros, you use a standard module, not a class module.

If all the data is in a table, you could use VLOOKUP to populate the other columns. If your data table looked like this...

A B C D E 1 P/N Connector Speed Size Manufacturer 2 123 DB-25 55 MPH Ladies' Small GM 3 234 USB Slow Men's Large Ford

You could put a part number in A5 and use these formulae to get the connector in B5 and Speed in C5:In B5:

=VLOOKUP(A5,$A$2:$E$3,2,0)

In C5:

=VLOOKUP(A5,$A$2:$E$3,3,0)

See VLOOKUP in the Excel Help files for a complete explanation.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History