vbproper

J

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
 
S

stevebriz

Joanne said:
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
Click to expand...

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
 
C

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
 
S

stevebriz

Chip said:
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
Click to expand...

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
 
D

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:
 
P

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
 
J

Joanne

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

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.
 
C

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
 
J

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.
 
C

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
 
P

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
 
Z

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Convert list to UPPER, lower & Proper cases. 21
Excel macros 1
Proper case code not working right 3
actual value of cell, not reference 3
exit for loop 2
Source Excel Template Pops Up when using a custom toolbar 3
delete row on basic of cell 1
Strange problem 1

Top