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