Convert list to UPPER, lower & Proper cases.

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

I'm trying to make either one of these subs do this.

Column A2 and down has a list of cities.
Some are three word cities, two word cities and one word cities.

This is a three city example. (A real list may be 300 - 400 + cities)

Salt Lake City
New York
Powell

Where I will get a list somewhere else on the sheet listing all the cities in lower case followed by all the cities in UPPER case and followed by all the cities in Proper case.

This first macro gives me a mixed bag of all the above with some duplicates and the list is 27 rows long.

I would expect a return of nine rows (with just three cities), three rows for each city, showing each case.

Like this:

salt lake city
new york
Powell
SALT LAKE CITY
NEW YORK
POWELL
Salt Lake City
New York
Powell


Option Explicit

Sub TriCaseORIG()

Dim cList As Range
Dim cCity As Range

Set cList = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)

On Error Resume Next 'In case of NO text constants.

Set cList = cList.SpecialCells(xlCellTypeConstants, xlTextValues)

If cList Is Nothing Then
MsgBox "Could not find any text."
On Error GoTo 0
Exit Sub
End If

For Each cCity In cList.SpecialCells(xlCellTypeConstants, xlTextValues)
cCity = StrConv(cCity, vbLowerCase)
cList.Copy Range("F" & Rows.Count).End(xlUp)(2)
Next cCity

For Each cCity In cList.SpecialCells(xlCellTypeConstants, xlTextValues)
cCity = StrConv(cCity, vbUpperCase)
cList.Copy Range("F" & Rows.Count).End(xlUp)(2)
Next cCity

For Each cCity In cList.SpecialCells(xlCellTypeConstants, xlTextValues)
cCity = StrConv(cCity, vbProperCase)
cList.Copy Range("F" & Rows.Count).End(xlUp)(2)
Next cCity

End Sub


Here I am attempting to read the city list into an array and convert the array to one of the cases and list it in F column. Then convert the array to another case and follow the one in already in F and then do the third case to follow the other two.

I was thinking using an array would be faster, but still struggle reading into an array as this errors out object required. Also not sure how I would change the case once the list was read into the array.

Thanks.
Howard


Sub TriCase()

Dim myRng As Range
Dim rngC As Range
Dim i As Long
Dim myArr As Variant


Set myRng = Array(Sheets("Sheet1").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row))
Set myRng = myRng.SpecialCells(xlCellTypeConstants, xlTextValues)

Application.ScreenUpdating = False

For Each rngC In myRng
ReDim Preserve myArr(myRng.Cells.Count - 1)
myArr(i) = rngC
i = i + 1
Next

With Sheets("Sheet1")
.Range("F2").Resize(columnsize:=myRng.Cells.Count) = myArr
End With

Application.ScreenUpdating = False

End Sub





Sub ChangeCase()
Dim Rng As Range
On Error Resume Next
Err.Clear
Application.EnableEvents = False
For Each Rng In Range("A2:A6").SpecialCells(xlCellTypeConstants, _
xlTextValues).Cells
If Err.Number = 0 Then
Rng.Value = StrConv(Rng.Text, vbUpperCase)
MsgBox "UPPER"
Rng.Value = StrConv(Rng.Text, vbLowerCase)
MsgBox "lower"
Rng.Value = StrConv(Rng.Text, vbProperCase)
MsgBox "Proper"
End If
Next Rng
Application.EnableEvents = True
End Sub
 
Sorry, slight typo with the loser case Powell city

salt lake city
new york
powell
SALT LAKE CITY
NEW YORK
POWELL
Salt Lake City
New York
Powell

Howard
 
Hi Howard,

Am Sun, 6 Apr 2014 00:15:25 -0700 (PDT) schrieb L. Howard:
Column A2 and down has a list of cities.
Some are three word cities, two word cities and one word cities.

This is a three city example. (A real list may be 300 - 400 + cities)

Salt Lake City
New York
Powell

Where I will get a list somewhere else on the sheet listing all the cities in lower case followed by all the cities in UPPER case and followed by all the cities in Proper case.
Click to expand...

try:

Sub Test()
Dim arrIn As Variant, arrOut As Variant
Dim myDic As Object
Dim LRow As Long
Dim rngC As Range
Dim i As Long

'Last row in column A
LRow = Cells(Rows.Count, 1).End(xlUp).Row

'changes all values to proper case
For Each rngC In Range("A2:A" & LRow)
rngC = WorksheetFunction.Proper(rngC)
Next
'Writes the values in an array
arrIn = Range("A2:A" & LRow)

'creates unique items
Set myDic = CreateObject("Scripting.Dictionary")
For i = LBound(arrIn) To UBound(arrIn)
myDic(arrIn(i, 1)) = arrIn(i, 1)
Next
'Writes the unique items in an array
arrOut = myDic.items

'Writes the values three times in column B
For i = 2 To 2 * myDic.Count + 2 Step myDic.Count
Cells(i, 2).Resize(myDic.Count, 1) =
WorksheetFunction.Transpose(arrOut)
Next

'Set first part to LCase
For Each rngC In Cells(2, 2).Resize(myDic.Count, 1)
rngC = LCase(rngC)
Next
'Set second part to UCase
For Each rngC In Cells(2 + myDic.Count, 2).Resize(myDic.Count, 1)
rngC = UCase(rngC)
Next
'Third part is already proper case
End Sub


Regards
Claus B.
 
try:



Sub Test()

Dim arrIn As Variant, arrOut As Variant

Dim myDic As Object

Dim LRow As Long

Dim rngC As Range

Dim i As Long



'Last row in column A

LRow = Cells(Rows.Count, 1).End(xlUp).Row



'changes all values to proper case

For Each rngC In Range("A2:A" & LRow)

rngC = WorksheetFunction.Proper(rngC)

Next

'Writes the values in an array

arrIn = Range("A2:A" & LRow)



'creates unique items

Set myDic = CreateObject("Scripting.Dictionary")

For i = LBound(arrIn) To UBound(arrIn)

myDic(arrIn(i, 1)) = arrIn(i, 1)

Next

'Writes the unique items in an array

arrOut = myDic.items



'Writes the values three times in column B

For i = 2 To 2 * myDic.Count + 2 Step myDic.Count

Cells(i, 2).Resize(myDic.Count, 1) =

WorksheetFunction.Transpose(arrOut)

Next



'Set first part to LCase

For Each rngC In Cells(2, 2).Resize(myDic.Count, 1)

rngC = LCase(rngC)

Next

'Set second part to UCase

For Each rngC In Cells(2 + myDic.Count, 2).Resize(myDic.Count, 1)

rngC = UCase(rngC)

Next

'Third part is already proper case

End Sub





Regards

Claus B.

--
Click to expand...


That does it just perfect! Thanks, Claus.

And also thanks for the comments within the code. That will help me in the future.

It seems every time I try to write to an array my examples syntax don't fit the scheme I am dealing with.

I had a web site that had many basic examples of the number of ways to write to an array but its gone from my Favorites list for some unknown reason.

Is there one you can recommend?

Thanks again.

Howard
 
Hi Howard,

Am Sun, 6 Apr 2014 02:22:18 -0700 (PDT) schrieb L. Howard:

please have a look:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "LCaseUCase"
There are three macros. One if you have no blank rows in column A, one
if you can sort column A to eliminate the blanks and one works with the
blanks.
It seems every time I try to write to an array my examples syntax don't fit the scheme I am dealing with.

I had a web site that had many basic examples of the number of ways to write to an array but its gone from my Favorites list for some unknown reason.

Is there one you can recommend?
Click to expand...

I am sorry. But I don't know books in english language that I can judge
them.


Regards
Claus B.
 
Hi again,

Am Sun, 6 Apr 2014 02:22:18 -0700 (PDT) schrieb L. Howard:
It seems every time I try to write to an array my examples syntax don't fit the scheme I am dealing with.
Click to expand...

if you want to redim an array in the code you have to declare it with
brackets:

Dim myArr() as variant


Regards
Claus B.
 
Hi Howard,

Am Sun, 6 Apr 2014 11:30:07 +0200 schrieb Claus Busch:
I am sorry. But I don't know books in english language that I can judge
them.
Click to expand...

a good way of learning is reading Garrys answers about arrays in the
different newsgroups. I guess I know much about arrays but Garry
surprises me again and again.


Regards
Claus B.
 
Here's my collection of case conversion subs, which you'll see have
been configured to work on selected cells. You could easily modify
these to accept a range *and/or* convert to a function so they return a
string. (I use them 'as is' for updating selected cells on-the-fly)


Sub ProperCase()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection: c.value = Application.Proper(c.value): Next
End Sub

Sub UpperCase()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection: c.value = UCase(c.value): Next
End Sub

Sub LowerCase()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection: c.value = LCase(c.value): Next
' For Each c In Selection: c.Value = UCase(Left(c.Value, 1)) &
LCase(Mid(c.Value, 2)): Next
End Sub

Sub SentenceCase()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection.Cells
s = c.value
Start = True
For i = 1 To Len(s)
Ch = Mid(s, i, 1)
Select Case Ch
Case ".", "?": Start = True
Case "a" To "z": If Start Then Ch = UCase(Ch): Start = False
Case "A" To "Z": If Start Then Start = False Else Ch =
LCase(Ch)
End Select
Mid(s, i, 1) = Ch
Next
c.value = s
Next
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Great. Thanks Garry.

As you say, pretty easy to adopt to a range as I did here.

Also noticed no screenupdating to true, but it still worked with the Proper Case example. (I just did add it here.)


Sub ProperCase()
Dim c As Range
Dim cList As Range
Application.ScreenUpdating = False
Set cList = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In cList: c.Value = Application.Proper(c.Value): Next
Application.ScreenUpdating = True

End Sub

Howard
 
Great. Thanks Garry.
As you say, pretty easy to adopt to a range as I did here.

Also noticed no screenupdating to true, but it still worked with the
Proper Case example. (I just did add it here.)


Sub ProperCase()
Dim c As Range
Dim cList As Range
Application.ScreenUpdating = False
Set cList = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In cList: c.Value = Application.Proper(c.Value): Next
Application.ScreenUpdating = True

End Sub

Howard
Click to expand...

ScreenUpdating automatically turns on when the code ends. It's
considered 'good practice' though, to always explicitly reset things
you explicitly change. Those subs are from my PERSONAL.XLS and so may
not even use Option Explicit in some modules, which is also my bad!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
ScreenUpdating automatically turns on when the code ends. It's

considered 'good practice' though, to always explicitly reset things

you explicitly change. Those subs are from my PERSONAL.XLS and so may

not even use Option Explicit in some modules, which is also my bad!<g>
Click to expand...

Okay, got it.
I have my setting to include Option Explicit so I always have, all on advice of folks like you.

Having trouble getting sentence case to work, probably because I did not know there was such an animal and not sure what a fully functional sentence case is to do.

My assumption is Cap first letter but what ending punctuation can be expected, if any?

What to dim Start as?

Howard


Sub SentenceCase()
Dim c As Range
Dim s As String, Ch As String
Dim Start
Dim i As Long

Application.ScreenUpdating = False
For Each c In Selection.Cells
s = c.Value
Start = True
For i = 1 To Len(s)
Ch = Mid(s, i, 1)
Select Case Ch
Case ".", "?": Start = True
Case "a" To "z": If Start Then Ch = UCase(Ch): Start = False
Case "A" To "Z": If Start Then Start = False Else Ch = LCase(Ch)
End Select
Mid(s, i, 1) = Ch
Next
c.Value = s
Next
Application.ScreenUpdating = False
End Sub
 
My assumption is Cap first letter but what ending punctuation can be
expected, if any?
Click to expand...

I'm pretty sure the macro ignores punctuation! It works on 'existing'
sentences that have mixed case content that doesn't display properly as
a sentence.
What to dim Start as?
Click to expand...

Boolean if you like, but Variant is fine if you don't want to do the
extra typing.<g>

Dim Start

or better yet...

Dim bStart

...since its datatype is Boolean in the context of this sub.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Arrays are a bit of a puzzle until you understand their structure. Once
you get there then working with them is a breeze! Here's some
pointers...

You can leave a declared array's elements blank (as Claus suggests) and
ReDim it once per usage if it's multi-dimensional, but you can only
progressively ReDim a 1D array.

Loading a range into a Variant results a 2D array consisting of [n]Rows
by [n]Cols. These will always be 1 based since there is no Rows(0) or
Columns(0) on a worksheet.

All other arrays are zero-based unless declared otherwise, *or* you
specify *Option Base 1* in the declarations section of the module
containing the code. I don't ever do this myself, but it seems to
attract interest to many when it shouldn't and so I recommend to avoid
using it.


Zero-based arrays:
These work well with most list control indexes.

Also works great when loading data from a delimited text file that has
been properly prepared with fieldnames in the first line. In this case,
the first record is MyArray(1) and the last record is UBound(MyArray).
Thus RecordCount = UBound(MyArray), and Record # of #Records is
always...

"Record " & MyArray(n) & " of " & UBound(MyArray)

So for example, you can 'dump' fieldnames into a ComboBox list like
this...

ComboBox1.List = Split(MyArray(0), ",")

...where its ListIndex starts at zero but the ListCount is
UBound(Split(MyArray(0), ","))+1. The same thing can be achieved as
follows...

Dim vTmp
vTmp = Split(MyArray(0), ",")
ComboBox1.List = vTmp

...but I don't see the point for using the extra steps.

Now in the case of a 1D array of single values...

ComboBox1.List = MyArray1D


Not surprisingly, you can't 'dump' a range directly into a list control
because the indexing is invalid due to a range array being 1-based-2D.
In this case you need to load the list into another array (or populate
each list item individually)...

With Application
Me.ComboBox1.List = .Transpose(.Index(Range("MyList"), 0, 1))
End With

...where "MyList" is a named range containing the items to display in
the ComboBox1.List.

(This can be confusing since you can 'dump' a range into a DV list
because it's 1-based since there's no Item(0) when enumerating a DV
list)

Also, VB[A] arrays are virtual meaning they only exist in memory.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Arrays are a bit of a puzzle until you understand their structure. Once

you get there then working with them is a breeze! Here's some

pointers...
Click to expand...


Thanks again for the info. I printed it out and will keep it handy.
If I could find that web site with the graphic examples and notes I used to have then I believe I could solve most of the mysteries I encounter with arrays.

I'll keep looking for it, found it once, can find it again.

Howard
 
My assumption is Cap first letter but what ending punctuation can
I'm pretty sure the macro ignores punctuation!
Click to expand...

I was wrong about this. The exclamation character is missing from the
list, though!

It works on 'existing' sentences that have mixed case content that
doesn't display properly as a sentence.
Click to expand...

For clarity, it doesn't work on paragraphs! That means the cell can
only contain a single sentence. Though, it would be easy to process
paragraphs and so could be modified to do so...

Selection.Value = SentenceCaseParagraph(Selection.Value)

Function SentenceCaseParagraph$(ByVal sText$)
Dim n&, Ch$, bStart As Boolean
bStart = True
For n = 1 To Len(sText)
Ch = Mid(sText, n, 1)
Select Case Ch
Case ".", "!", "?": bStart = True
Case "a" To "z": If bStart Then Ch = UCase(Ch): bStart = False
Case "A" To "Z": If bStart Then bStart = False Else Ch =
LCase(Ch)
End Select
Mid(sText, n, 1) = Ch
Next
SentenceCaseParagraph = sText
End Function

...which will work for any number of paragraphs in a selected cell.
Optionally, you could process an entire range by passing each cell's
value to the function.
Boolean if you like, but Variant is fine if you don't want to do the
extra typing.<g>

Dim Start

or better yet...

Dim bStart

..since its datatype is Boolean in the context of this sub.
Click to expand...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Not surprisingly, you can't 'dump' a range directly into a list
control because the indexing is invalid due to a range array being
1-based-2D. In this case you need to load the list into another array
(or populate each list item individually)...

With Application
Me.ComboBox1.List = .Transpose(.Index(Range("MyList"), 0, 1))
End With

..where "MyList" is a named range containing the items to display in
the ComboBox1.List.
Click to expand...

I guess it would be prudent for me to explain how the above code works!

Just as we can ref a range array via the INDEX() function, so too can
we ref a row or col of a 2D array. In the case of a list control, we
need to transpose the elements to a vertical list since, by default,
Index() results a horizontal list...

Doing a row of a 2D array:
With Application
Me.ComboBox1.List = .Transpose(.Index(Range("MyList"), 1, 0))
End With

...where "MyList" is a horizontal named range this time.

This is not necessary, though, when populating a multi-column list from
a 2D array...

Dim vData
vData = ActiveSheet.UsedRange
With ListBox1
.ColumnCount = UBound(vData, 2) '//# of cols in the array
.List = vData
End With

...where ListCount will be UBound(vData), but the first item index for
both rows/cols is zero.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
This is not necessary, though, when populating a multi-column list
from a 2D array...

Dim vData
vData = ActiveSheet.UsedRange
With ListBox1
.ColumnCount = UBound(vData, 2) '//# of cols in the array
.List = vData
End With

..where ListCount will be UBound(vData), but the first item index for
both rows/cols is zero.
Click to expand...

Actually, that works with a 1 column/row list so long as you specify
its ColumnCount property. So...

Dim vData
vData = Range("A1:A3")
With ListBox1
.ColumnCount = UBound(vData, 2) '//# of cols in the array
.List = vData
End With

...results
a1
a2
a3

...and...
Dim vData
vData = Range("A1:C1")
With ListBox1
.ColumnCount = UBound(vData, 2) '//# of cols in the array
.List = vData
End With

...results
a1 a2 a3

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
For clarity, it doesn't work on paragraphs! That means the cell can
only contain a single sentence.
Click to expand...

I'm also wrong about this.., so long as end of sentence punctuation is
"." or "?"! Changing the sub as folows makes it suitable for multiple
sentences/paragraphs...

Sub SentenceCase()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Selection.Cells
s = c.value
Start = True
For i = 1 To Len(s)
Ch = Mid(s, i, 1)
Select Case Ch
Case ".", "?", "!": Start = True
Case "a" To "z": If Start Then Ch = UCase(Ch): Start = False
Case "A" To "Z": If Start Then Start = False Else Ch =
LCase(Ch)
End Select
Mid(s, i, 1) = Ch
Next
c.value = s
Next
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Dim vData
vData = Range("A1:A3")

With ListBox1

.ColumnCount = UBound(vData, 2) '//# of cols in the array

.List = vData

End With



..results

a1

a2

a3



..and...

Dim vData

vData = Range("A1:C1")

With ListBox1

.ColumnCount = UBound(vData, 2) '//# of cols in the array

.List = vData

End With



..results

a1 a2 a3
Click to expand...

If a list box was not used what would a 'typical' code look like?
And to return the data would you just use something like
.Range("F1") = vData
if so does F1 need to be Resized?

With "What instead of List Box"

.ColumnCount = UBound(vData, 2) '//# of cols in the array

.List = vData

End With

Howard
 
Dim vData
If a list box was not used what would a 'typical' code look like?
And to return the data would you just use something like
.Range("F1") = vData
if so does F1 need to be Resized?

With "What instead of List Box"

.ColumnCount = UBound(vData, 2) '//# of cols in the array

.List = vData

End With

Howard
Click to expand...

Yes! Any time you 'dump' an array into a worksheet the target range
needs to match the size of the arrays for both rows & cols!

Range("F1").Resize(UBound(vData, UBound(vData, 2)) = vData

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top