View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
John[_116_] John[_116_] is offline
external usenet poster
 
Posts: 6
Default Function that replaces Text in cell

On Mar 27, 1:42 pm, "Trevor Shuttleworth"
wrote:
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


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


- 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.