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

LVL 11
bsharathAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

bsharathAuthor Commented:
Thanks
I get type mismatch
Error 2015
wchhCommented:
May I know that is the content that cause the error?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Curt
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?
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

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
bsharathAuthor Commented:
Sorry still same error
Not able to find which cell its erroring
wchhCommented:
<---Can i know which cell its erroring?
Add Watch (Debug->add Watch) to Cell.Address
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
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.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.