Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 "John" wrote in message ups.com... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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. "Trevor Shuttleworth" wrote in message ... 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 "John" wrote in message ups.com... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
On Mar 27, 8:02 pm, JLGWhiz wrote:
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- Hide quoted text - - 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function that replaces Text in cell
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 "John" wrote in message oups.com... On Mar 27, 8:02 pm, JLGWhiz wrote: 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replaces formula to its value | Excel Worksheet Functions | |||
#Value replaces formula result when file is opened. But why? | Excel Discussion (Misc queries) | |||
Opening a second worksheet replaces the current | Excel Discussion (Misc queries) | |||
How do you write an if statement that replaces #DIV/0! with 0 | Excel Discussion (Misc queries) | |||
Pasted text from Excel to Word replaces previous paragraph | Excel Programming |