Function that replaces Text in cell

[John]

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

 

[Trevor Shuttleworth]

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]

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









- 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

 

[Trevor Shuttleworth]

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]

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











- Show quoted text -

I would like the name replaced in the same cell is what I'm trying to
do (in cell C4).
If, for example, I put: 3D ventures in cell C4
and I put: =ConvertVenName(C4) in cell C4,
I want cell C4 to show: 3-D Ventures Ltd.

 

[Trevor Shuttleworth]

You can't do what you want to. You either have a value in a cell or you
have a formula. If you have =ConvertVenName(C4) in cell C4, that's
why you get a circular reference ... and you have overwritten the previous
contents of cell C4 with your formula ... and that's why when you track it
you don't see what you expect to see.

You could try using a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo CalcBack
Application.EnableEvents = False
Select Case Target
Case "3D ventures"
Target = "3-D Ventures Ltd."
Case "Co-op Fuel"
Target = "Co-op Fuel"
Case "Craig waterwell"
Target = "Craig Waterwell"
Case "Denim"
Target = "Denim Pipeline"
Case "Deno"
Target = "Deno Contracting Ltd."
Case "DFI"
Target = "DFI"
Case "DRW Roto"
Target = "D.R.W. Rotoslashing Ltd."
Case "E&L Trucking"
Target = "E&L Trucking"
Case "Echo"
Target = "Echo"
Case "ESS"
Target = "ESS Support Services Worldwide"
Case "Fabcor"
Target = "Fabcor"
Case "Foley Inspection"
Target = "Foley Inspection Services Inc."
Case Else
Target.Interior.Color = vbRed
End Select
CalcBack:
Application.EnableEvents = True
End Sub

This will be effective on EVERY cell in the worksheet. You may want to be a
little selective about which cells, rows or columns you apply this to.

Regards

Trevor

 

[Trevor Shuttleworth]

Note that the Select Case compare is case sensitive so you'll need to type
EXACTLY what you show in your list.

Might be better to say:

Select Case LCase(Target)
Case "3d ventures"
Target = "3-D Ventures Ltd."
etc

I trust this is the full list as it isn't exactly scalable ... and it means
you have to update your code every time you add a vendor.

 

[JLGWhiz]

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

 

[John]

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








- Show quoted text -

Thanks Trevor & Whiz for your help. Not being able to change the list
in place sucks but it's not unlivable. As far as the code not being
scalable, I know, I don't expect to have to make changes and I was not
sure how to allow the user to add, delete and modify vendor names
list.

Cheers
John

 

[Trevor Shuttleworth]

John

you *can* change the names in situ but you'd have to use the worksheet
change event. You can restrict that to a range, a column or a row,
whatever.

I'd be inclined to put the vendor abbreviations and names into separate
lookup table and, when you type something, use the worksheet change event to
look it up. Easily scalable.

Regards

Trevor