function returning Text to cell

[John]

First thanks for taking the time to help a poor soul out.
Here is my issue.
I have a list of vendor names. The vendor names are some what of an
abbreviation of the actual name. I want to write a function that will
take in the range of Vendor abbreviations and replace it with the
full
name. I thought a 'Select Case' would be easiest. I also would like to
return the name to the cell itself not another cell. The cell is not
turning red either. Here is my code :
Function ConvertVenName(Ven As Range) As String
Dim xlCalc As XlCalculation
Dim savScrnUD As Boolean
savScrnUD = Application.ScreenUpdating
Application.ScreenUpdating = True
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error GoTo CalcBack
Select Case Ven.Value
Case "3D ventures"
ConvertVenName = "3-D Ventures Ltd."
Case Else
Ven.Interior.Color = vbRed
End Select

Application.Calculation = xlCalc
Application.ScreenUpdating = savScrnUD
Exit Function
CalcBack:
MsgBox Err.Description
Application.Calculation = xlCalc
Application.ScreenUpdating = savScrnUD
End Function

cheers
John

 

[Earl Kiosterud]

John,

A function can only return a value, and only where it appears. So the abbreviation would
have to be on one cell (A2, mayhaps), and =ConvertVenName(A2) in another (e.g.: B2). In B2,
you'd see the returned value of the function. But it can't do stuff like change the cell
formatting (color). Also, using a Select Case isn't the normal practice, especially where
the abbreviations and corresponding full names might change.

If you want to change the abbreviations in situ (A2), you could use AutoCorrect, or an
event-fired sub (Worksheet_Change) that changes them as soon as they're typed in. If you
want to put the abbreviations in one cell (A2) and see the corresponding full name in
another (B2), make a table of the abbreviations somewhere and in B2, use =VLOOKUP(A2, Table,
2, FALSE).