Change case code need a fix

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

ASKER

Thanks
I get type mismatch
Error 2015

May I know that is the content that cause the error?

ASKER

Now it does not change as #Value
But get this error and code exits

Possible to attach sample?

What is the special character that causes the #Value error?

Curt

ASKER

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?

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

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

ASKER

Sorry still same error
Not able to find which cell its erroring

<---Can i know which cell its erroring?
Add Watch (Debug->add Watch) to Cell.Address

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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