Can you change text with a macro?

[vince99]

is it possible to have a macro go in and change the text in an excel file? I have a very large file and I need to replace some of the text with a number. For example, column 2 is a list of companies. What I need to do is change the list of companys from text to a code, depending on the company. How can I do this so that each week, I can just apply a macro to the file and it will do it for me ?

Thanks guys

 

[ETID]

!!!!!In a copy of your workbook!!!!!

Make a 2 col lookup table, with you suppliers and matching codes...I used "m1:n4" col m being supplier col n being the code. ****you will have to edit the code below to reflect your table range***



The following code assumes that your supplier names are in a continuous col, (col a) with no breaks in the data.

and no header row....



---------------------------------------------

Sub code_it()

On Error GoTo err_hit
Dim r As Integer 'row of 1st supplier
Dim c As Integer 'supplier column

r = 1: c = 1 'edit these values to reflect your worksheet layout, c is the col that you supplier is in.

Do While Cells(r, c) <> &quot;&quot;
Cells(r, c) = WorksheetFunction.VLookup(Cells(r, c), Range(&quot;m1:n4&quot, 2, False)
r = r + 1
Loop

GoTo end_it

err_hit:
MsgBox (&quot;Code not found for supplier &quot; + Cells(r, c)): r = r + 1: Resume

end_it:

End Sub