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.

--
Regards,
Tom Ogilvy

"Catalin" wrote:

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.

"Martin" wrote:

Couldn't you just use VLOOKUP?

"Catalin" wrote:

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