Your code works fine.

If, for example, I put: 3D ventures in cell C4
and I put: =ConvertVenName(C4) in cell D4,
cell D4 shows: 3-D Ventures Ltd.

I'm a little confused as to how you are trying to use this function.

You can only return a value, you can't change the Excel environment ... you
can't format cells.

Regards

Trevor


"John" wrote in message
oups.com...
On Mar 26, 4:14 pm, "Trevor Shuttleworth"
wrote:
John

a function can only return a value to the cell in which it resides. You
can't loop through a range of cells

But then you have:

For Each Cll In Ven
Select Case Cell

So I don't think it would work.

You either need to run this as a subroutine or as a function referring to
a
specific cell.

Regards

Trevor

"John" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Thanks for the response.
Ok so I took the for loop out. here is what it looks like now:

Function ConvertVenName(Ven As Range) As String
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
Select Case Ven
Case "3D ventures"
name = "3-D Ventures Ltd."
Case Else
Ven.interior.Color = vbRed
End Select
ConvertVenName = name

CalcBack:
Application.Calculation = xlCalc
Application.ScreenUpdating = savScrnUD
End Function

When a watch is placed on Ven.Text and Ven.Value it shows "0" and
"Empty", I don't understand why. I also get a circular ref error using
the above code.

Cheers,
John