• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Change case code need a fix

Hi All,

The below code changes the selected column case
If there is a special character like -
it makes the cell #Value

Can anyone help fix this
Thanks
Public Sub ProperCaseSelection()

   Dim Cell As Range
   
   Application.EnableEvents = False
   For Each Cell In Selection
      If Not Cell.HasFormula Then If Not IsNumeric(Cell) And Len(Cell) > 1 Then Cell = Application.Proper(Cell)
   Next Cell
   Application.EnableEvents = True

End Sub

Open in new window

0
bsharath
Asked:
bsharath
  • 5
  • 5
  • 5
  • +1
2 Solutions
 
wchhCommented:
Try macro below
Public Sub ProperCaseSelection()

   Dim Cell As Range
   
   Application.EnableEvents = False
   For Each Cell In Selection
      If Not Cell.HasFormula Then If Not IsNumeric(Cell) And Len(Cell) > 1 Then Cell = Application.Proper(Cell)
      'Insert heaer
      Cell.Value = Replace(Cell.Value, "-", "#")
   Next Cell
   Application.EnableEvents = True

End Sub

Open in new window

0
 
bsharathAuthor Commented:
Thanks
I get type mismatch
Error 2015
0
 
wchhCommented:
May I know that is the content that cause the error?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bsharathAuthor Commented:
Now it does not change as #Value
But get this error and code exits
0
 
wchhCommented:
Possible to attach sample?
0
 
Curt LindstromCommented:
What is the special character that causes the #Value error?

Curt
0
 
bsharathAuthor Commented:
I tried removing some content and running so could send the file and those content i dont get the error. Just the critical one that cannot be shared gets the error.

Nothing different found

Can i know which cell its erroring?
0
 
wchhCommented:
May be the negative number is replaced by #
Try Amended Code
Public Sub ProperCaseSelection()

   Dim Cell As Range
   
   Application.EnableEvents = False
   For Each Cell In Selection
      If Not Cell.HasFormula Then If Not IsNumeric(Cell) And Len(Cell) > 1 Then Cell = Application.Proper(Cell)
      'Insert here
       If Not (IsNumeric(Cell.Value) Or IsError(Cell.Value)) Then Cell.Value = Replace(Cell.Value, "-", "#")
   Next Cell
   Application.EnableEvents = True

End Sub

Open in new window

0
 
Curt LindstromCommented:


Set a break point (F9) in the VBE code window and run your code by pressing F5 several times until you get the error. You can read the value of "Cell" in the code when the code stops by moving the cursor to the "Cell" in the line
For Each Cell In Selection

You can also add "Cell" as a watch in the Debug menu.

Curt
0
 
bsharathAuthor Commented:
Sorry still same error
Not able to find which cell its erroring
0
 
wchhCommented:
<---Can i know which cell its erroring?
Add Watch (Debug->add Watch) to Cell.Address
0
 
Curt LindstromCommented:
Try this code to get the address and value of the cell with the error.
 
Public Sub ProperCaseSelection()

   Dim Cell As Range
   
   Application.EnableEvents = False
   For Each Cell In Selection
   On Error GoTo ErrorHandler
      If Not Cell.HasFormula Then If Not IsNumeric(Cell) And Len(Cell) > 1 Then Cell = Application.Proper(Cell)
   Next Cell
   Application.EnableEvents = True
   Exit Sub 'to avoid error handler
ErrorHandler:
MsgBox (Cell.Address & " content is " & Cell)
   Application.EnableEvents = True
End Sub

Open in new window

Curt
0
 
bsharathAuthor Commented:
When run some cells change as this

#VALUE!

When run 2nd time is when i get the error and points to the cells where content is #VALUE!

Not sure why the code changes the content to #VALUE!


before change the content in the cell is

MS office 2010, Oracle 11g client, SQL 2008 Client, Vs 2010, VS 6.0, Citrix Xen App Client 11,Toad 9.7, WebEx 3.0, Flash Bus Spectrim Lite video card 1.2, Flash Bus Spectrim Lite video card driver install 3.2a, Apache Tomcat 6.0, Apache Tomcat 7.0, 7Scan 2.1.
0
 
Rory ArchibaldCommented:
Change the code to:
Public Sub ProperCaseSelection()

   Dim Cell As Range
   
   Application.EnableEvents = False
   For Each Cell In Selection
      If Not Cell.HasFormula Then If Not IsNumeric(Cell) And Len(Cell) > 1 Then Cell.Value = Application.Proper(Cell.Value)
   Next Cell
   Application.EnableEvents = True

End Sub

Open in new window

0
 
Curt LindstromCommented:
That string is 259 characters long. It seems like Proper can only handle 255 characters. You can test this by removing the first 4 characters in the cell and the code will work.
Change it like this and then try:

ffice 2010, Oracle 11g client, SQL 2008 Client, Vs 2010, VS 6.0, Citrix Xen App Client 11,Toad 9.7, WebEx 3.0, Flash Bus Spectrim Lite video card 1.2, Flash Bus Spectrim Lite video card driver install 3.2a, Apache Tomcat 6.0, Apache Tomcat 7.0, 7Scan 2.1.

It doesn't solve your problem but it explains why it happens.

Cheers,
Curt
0
 
Curt LindstromCommented:
Sorry Rory I posted before refreshing. How come the first solution only can handle 255 characters but your solution seem to do the 32767 characters as specified by MS?

Curt
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 5
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now