Shouldn't your cases all be name = instead of cll = ?

"John" wrote:

Hi All,

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. Here is my code (I
have commented out all unnecessary code):
Function ConvertVenName(Ven As Range) As String
Dim Cll As Range
Dim name 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
For Each Cll In Ven
Select Case Cell
Case "3D ventures"
name = "3-D Ventures Ltd."
' Case "Co-op Fuel"
' Cll = "Co-op Fuel"
' Case "Craig waterwell"
' Cll = "Craig Waterwell"
' Case "Denim"
' Cll = "Denim Pipeline"
' Case "Deno"
' Cll = "Deno Contracting Ltd."
' Case "DFI"
' Cll = "DFI"
' Case "DRW Roto"
' Cll = "D.R.W. Rotoslashing Ltd."
' Case "E&L Trucking"
' Cll = "E&L Trucking"
' Case "Echo"
' Cll = "Echo"
' Case "ESS"
' Cll = "ESS Support Services Worldwide"
' Case "Fabcor"
' Cll = "Fabcor"
' Case "Foley Inspection"
' Cll = "Foley Inspection Services Inc."
' Case "Foley Inspection "
Cll = "Foley Inspection Services Inc."
' Case Else
' Cll.Interior.Color = vbRed
End Select
ConvertVenName = name
Next Cll
CalcBack:
MsgBox Err.Description
'Application.Calculation = xlCalc
'Application.ScreenUpdating = savScrnUD
End Function

I keep getting a zero returned to the cell or a circular ref error.
This should be easy but I can't seem to see what I'm doing wrong.
Please help.

John