Create Select Case Function

[Guest]

COLUMN A COLUMN B
Apple 1
Orange 2
Kiwi 3
Apple 1
etc.
What should be the VBA code to auto fill the column B given the values of
column A. A couple of years ago I solved this problem but can not remember
how I did that, all I remember is that I created a function (that included
the select case because of the number of entries) and accessed it from the
worksheet.
Thanks

 

[Guest]

Couldn't you just use VLOOKUP?

 

[Guest]

You're right, this could be a solution, but remembering the fact that I
already did this once using VBA I'd like to get some advice on how to solve
this using VBA.
Many thanks for your answer.

 

[Guest]

What I managed to do is to get the value from the worksheet into the VBA
function, processed the information but I still have difficulties in sending
it back to the worksheet in a corresponding cell. Your help is much
appreciated.

 

[Guest]

Public Function ConvertData(rng as Range)
Dim n as Long
set rng1 = rng(1)
select Case lcase(rng1.value)
Case "apple" : n=1
Case "orange" : n=2
Case "kiwi" : n= 3
Case Else
n = 0
End Select
ConvertData = n
End Function

The code must be placed in a general module (insert=>Module in the VBE), NOT
in a sheet module or the thisworkbook module.

 

[Guest]

Tom many thanks for your answer.

But I still can not get this code to do the job I'm expecting.
in the worksheet I wrote "=convertdata(E2)", is this correct? In the
mentioned cell as an answer I get always 0.

 

[Guest]

Many Thanks to All. I just found the asnwer.