Solved: Changing Names to Numbers

[neditheg]

Hy guys,


First of all sorry for my poor english language :|


I have a column with people names, and I want to change this names in numbers.

for example :

I have this column:

albert
mario
maria
albert
andrew
john
albert
maria
mario
john
andrew

now i want to transform this names in numbers

albert=210
mario=211
maria=212
andrew=213
john=214


my new column must look like this:

210
211
212
210
213
214
210
212
211
214
213

Thanks!!

[rangudu_2008]

Check out the attachment...

[rangudu_2008]

Check out the attachment...

[Bob Phillips]

Assuming you don't have the lookup values in a worksheet



Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim LastRow As Long
With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To LastRow

With .Cells(i, TEST_COLUMN)

Select Case LCase(.Value)

Case "albert": .Value = 210
Case "mario": .Value = 211
Case "maria": .Value = 212
Case "andrew": .Value = 213
Case "john": .Value = 214
End Select
End With
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

[neditheg]

thanks xld , this code works , but I still have a problem , in my database I have at least 56 diferent operators names.

<< Case "albert": .Value = 210
Case "mario": .Value = 211
Case "maria": .Value = 212
Case "andrew": .Value = 213
Case "john": .Value = 214 " >>

If i must change in this code albert, john etc... with the operators names .. ...I still have a lot of work to do. I'll put an attachement with a sample of my DB.

Thanks again!

[Bob Phillips]

You have to do the work somewhere. Either update the code, or create a list in a worksheet that can be checked against.

[Aussiebear]

If i must change in this code albert, john etc... with the operators names .. ...I still have a lot of work to do.

HI neditheg,

Perhaps you wrote this with a different concept in mind, but the reality is as Bob has suggested. You do indeed have some work to do, but its a whole lot less than if you didn't have the code as he has provided.

[neditheg]

I know that :) and i've made that list to update the code.

Thanks!!

[Ago]

maybe you can use the replace function?
it will do the job, and with less typing than makeing a macro for it.

[neditheg]

did u see my db? i've made the macro thanks to xld, but i'll try ur solution too if u give me the right function format :)