vbproper

[Joanne]

I need to change a list into vbproper case.
Here is the code I am using, which works just fine.
Public Sub ProperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub
My problems is it lower cases the second letter in the State, such as
MN to Mn - I would like to keep it in upper case if possible.
Also in an address such as 23W482 it lowers the 'w'. I think I should
be able to keep that upper if I knew how to tell the macro to skip
alphas that are in the middle of numerics.
Can anyone help me out with these two little problems?
TIA
Joanne

 

[stevebriz]

I need to change a list into vbproper case.
Here is the code I am using, which works just fine.
Public Sub ProperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub
My problems is it lower cases the second letter in the State, such as
MN to Mn - I would like to keep it in upper case if possible.
Also in an address such as 23W482 it lowers the 'w'. I think I should
be able to keep that upper if I knew how to tell the macro to skip
alphas that are in the middle of numerics.
Can anyone help me out with these two little problems?
TIA
Joanne

Not sure if this will work for you but here is an idea.

vbProperCase - Converts the first letter of every word in string to
uppercase.

vbUpperCase - Converts the string to uppercase characters.


'----------------------------
'Converts Text to Upper Case
'----------------------------
Public Sub ConvertToUpper()
'Test if control contains text
If IsNull(Screen.ActiveControl) = False Then
'convert text to Proper Case
Screen.ActiveControl = StrConv(Screen.ActiveControl,
vbUpperCase)
End If
End Sub

 

[Chip Pearson]

Screen.ActiveControl = StrConv(Screen.ActiveControl,

In VBA, there is no Screen object, as there is VB6. If you're
going to post code, post code that works.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[stevebriz]

In VBA, there is no Screen object, as there is VB6. If you're
going to post code, post code that works.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

Oops sorry.!! your are right.

Public Sub upperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbUpperCase)
End If
Next Rng
End Sub

 

[Dave Peterson]

But that changes all the text to upper case. Not quite what Joanne requested.

To Joanne: I think you're going to have to look at it character by
character--if you really want the case the way you want.

And you'll have to be careful.

Does ct become CT or Ct?

Is it an abbreviation for Court or Connecticut?

I don't think it's a trivial task. (I'd follow Steve's suggestion and use all
upper case!)


stevebriz wrote:

 

[Peter T]

Hi Joanne,

Not sure how far you need go but try and adapt the following to your needs

Option Explicit
Function ProperCustom(sIn As String) As String
Dim i As Long, j As Long
Dim ba() As Byte
Dim sa
Dim sTmp As String
Dim sOut As String

sOut = UCase(sIn)
sa = Split(sOut, " ") ' n/a in XL97
If IsArray(sa) Then
sOut = ""
'to do - handle if not an array, ie no spaces
For i = LBound(sa) To UBound(sa)
ba = sa(i)
's = ba
If sa(i) Like "*#*" Then
For j = 2 To UBound(ba) Step 2
If ba(j) > 64 And ba(j) < 92 Then
ba(j) = ba(j) + 32
End If
Next

ElseIf UBound(ba) = 3 Then
If ba(0) > 64 And ba(2) > 64 And ba(0) < 92 And ba(2) < 92
Then
ba(2) = ba(2) + 32
End If
End If
If i Then
sTmp = ba
sOut = sOut & " " & sTmp
Else: sOut = ba
End If
Next

End If
ProperCustom = sOut
End Function

Sub test()
Dim s As String
Dim s2 As String
s = "abc ab a123bc4"
s2 = ProperCustom(s)
Debug.Print s
Debug.Print s2
'abc ab a123bc4
'ABC Ab A123bc4

End Sub

Only lightly tested, assumes the string includes spaces, also if the first
letter of mixed letters & numbers it should be upper but others lower.
Muliple spaces will trim to singles.

Need to be aware this string > byte array > string method is only reliable
with Latin text and characters 1-127.

Regards,
Peter T

 

[Joanne]

What?!

In VBA, there is no Screen object, as there is VB6. If you're
going to post code, post code that works.

 

[Joanne]

Thanks Dave - I'll give it some consideration. Didn't realize it could
be so complicated - shows what I know about Excel VBA

 

[Joanne]

Thanks for your consideration Steve
Thing is, the file is currently in Upper Case and I was hoping to
change it to VbProper, but when I run the code I noticed that MN
becomes Mn and 28W124 becomes 28w124. These are the two issuses I was
hoping to address.

 

[Dave Peterson]

Chip was telling Steve that Steve's code wouldn't work in excel's VBA.

 

[Chip Pearson]

What "What?!"?

I was indicating that code posted by stevebriz wouldn't work.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Joanne]

I get it - a bit slow on the uptake on this Monday
Thanks Chip for your input

 

[Joanne]

Got it - like I told Chip, I am a bit slow on this Monday
Thanks for your efforts, and of course, the code Steve offered doesn't
work, just as you and Chip indicated. I guess I am back at the drawing
board.

 

[Chip Pearson]

I get it - a bit slow on the uptake on this Monday

Become an independent consultant. When business is good, every
day's a Monday. When business is weak, every day's a Saturday.
Both have their unique charm.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peter T]

I guess I am back at the drawing board.

Did you try the example I posted, perhaps it might work on Tuesday!

Regards,
Peter T

 

[Jon Peltier]

LOL

- Jon

 

[Zone]

Joanne,
I've had something similar to this, so I'll jump in. This should
fix the state problem. It presumes that the state will always have a
space before and after it. DC is included but not PR (Puerto Rico)!
Notice also the change to your routine, below.
James

Function FixState(FixStr As String) As String
Dim StateStr As String, z As Integer, Loc As Integer
StateStr = " AL AK AZ AR CA CO CT DE DC FL GA HI ID IL IN IA KS KY
LA " _
& " ME MD MA MI MN MS MO MT NE NV NH NJ NM NY NC ND OH OK OR " _
& " PA RI SC SD TN TX UT VT VA WA WV WI WY "
For z = 1 To Len(FixStr)
Loc = InStr(StateStr, UCase(Mid(FixStr, z, 4)))
If Loc > 0 Then
FixStr = Left(FixStr, z - 1) & Mid(StateStr, Loc, 4) &
Right(FixStr, Len(FixStr) - z - 3)
Exit For
End If
Next z
FixState = FixStr
End Function