Replace text with numbers.

[Josh]

I'm working with importing text files into Excel that have account
names listed in one column and I'd like to repalce the account name
with an account number. For Example, I would like to replace the name
ADVERTISING with the number 63100 and I would like to replace the name
PURCHASES with the number 51000. I would like to know if someone
would be able to get me started with an Excel macro that I would just
be able to continue the same formatting, adding records to achieve the
same results but within a shorter period of time. Does anybody have
an idea of how to help me get this one started?

Thanks for helping,

Josh

 

[Tom Ogilvy]

Columns("A:A").Select
Selection.Replace What:="advertising", Replacement:="61300",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Purchases", Replacement:="51000",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False


or
Sub MakeReplacements()
dim varr as variant, varr1 as variant, i as long
varr = Array("advertising", "purchases")
varr1 = Array(63100, 51000)
for i = lbound(varr) to ubound(varr)
Columns(A:A).Replace What:=varr(i), Replacement:=varr1(i), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next i
End Sub

 

[Nigel]

Take a look at the standard Excel vlookup function, in any event you'll need
a look up table to convert from the text data to a numerical value, perhaps
on another sheet. Unless you wish to hard code these into a program (not
usually recommended for dynamic data). How do you intend to establish the
translation and maintain it?

Cheers
Nigel

 

[Josh Webskowski]

There appears to be syntax errors in the following assertions:

Columns(A:A).Replace What:=varr(i), Replacement:=varr1(i), _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

and

Selection.Replace What:="advertising", Replacement:="61300",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="Purchases", Replacement:="51000",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False

They all appear in red when I copy/paste it into VB editor. I have
Excel 2000.

 

[Tom Ogilvy]

You can ignore these since you have posted again with different
requirements.