how to format column D to Paste a value based on Column A

attach please a sample file with current data and desired result

Hi, desquaninc,

how do we know that ABW is Jakes supply store? You could use the string strVendor you get from the code to use WorksheetFunction.VLookUp for the name in Column D.

To give you a start (this will print the codes to the immediate Window):

Please Login or Register  to view this content.

Ciao,
Holger

where I can see data ? where desired result ? where vendors code ?

Hi, desquaninc,

no new information in that workbook - please don´t post the same data more than once but care to deliver what´s been asked. Besides: my code takes care of the vendor code (at least with your sample data) but I won´t get on as the other informtion (the table with the data for the vendors or the information where it is located) is missing.

@patek45:
I´m glad to tell you there is a preview which might be used prior to submitting an answer (there can´t be any error in your browser as you have edited your own post prior to answering - sometimes it´s nice that vBulletin states this).

Ciao,
Holger

Hi, desquaninc,

regarding your opening post

I need to know how to make excel look at a value in column A and based on what is there place the correct words in column D

This step might be resolved by using a Formula. Please enter this formula into E1 in your sample and then drag it down to the end of the data set:

Formula:

Please Login or Register  to view this content.

The second step would be to use this string in a lookup formula to find the code and deliver the long name. Let´s assume this list is on a sheet named Data with the codes in Column A and the company name in Column B then please enter this formula into F1 on the sample (Sheet Data needs to be in the same workbook else needs to be "qualified" with the workbook´s name):

Formula:

Please Login or Register  to view this content.

Drag down to the end of the data. (It´s this information that is missing in your workbook.)

These two steps can be put into one formula for D1 like

Formula:

Please Login or Register  to view this content.

which isn´t that self-explanatory as the two steps before.

VBA:
This process can be automated via VBA to paste values instead of formulas. Assuming the table on sheet Data as before the code may look like

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

You would need to save the workbook as macro enabled, set the settings in the options for the trusted locations (if not already done) and allow macros to be executed. You should try the macro on a copy (maybe copy the sheet inside the workbook for a test and clear column D there to see if the macro does what it is supposed to do).

HTH,
Holger