Convert list to UPPER, lower & Proper cases.

Discussion in 'Excel Programming' started by L. Howard, Apr 6, 2014.

  1. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 6, 2014
    #1
    1. Advertisements

  2. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 6, 2014
    #2
    1. Advertisements

  3. L. Howard

    Claus Busch Guest

    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.


    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.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Apr 6, 2014
    #3
  4. L. Howard

    L. Howard Guest


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



    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
     
    L. Howard, Apr 6, 2014
    #4
  5. L. Howard

    Claus Busch Guest

    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?


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


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Apr 6, 2014
    #5
  6. L. Howard

    Claus Busch Guest

    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.


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

    Dim myArr() as variant


    Regards
    Claus B.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Apr 6, 2014
    #6
  7. L. Howard

    Claus Busch Guest

    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.


    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.
    --
    Vista Ultimate SP2 / Windows7 SP1
    Office 2007 Ultimate SP3 / 2010 Professional
     
    Claus Busch, Apr 6, 2014
    #7
  8. L. Howard

    GS Guest

    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
     
    GS, Apr 6, 2014
    #8
  9. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 6, 2014
    #9
  10. L. Howard

    GS Guest

    > 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


    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
     
    GS, Apr 6, 2014
    #10
  11. L. Howard

    L. Howard Guest

    On Sunday, April 6, 2014 11:05:08 AM UTC-7, GS wrote:
    > > 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

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


    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
     
    L. Howard, Apr 6, 2014
    #11
  12. L. Howard

    GS Guest

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


    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?


    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
     
    GS, Apr 6, 2014
    #12
  13. L. Howard

    GS Guest

    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
     
    GS, Apr 6, 2014
    #13
  14. L. Howard

    L. Howard Guest

    On Sunday, April 6, 2014 12:16:15 PM UTC-7, GS wrote:
    > 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...
    >



    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
     
    L. Howard, Apr 6, 2014
    #14
  15. L. Howard

    GS Guest

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

    >
    > I'm pretty sure the macro ignores punctuation!


    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.


    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.

    >
    >>
    >> What to dim Start as?

    >
    > 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
     
    GS, Apr 6, 2014
    #15
  16. L. Howard

    GS Guest

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


    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
     
    GS, Apr 6, 2014
    #16
  17. L. Howard

    GS Guest

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


    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
     
    GS, Apr 6, 2014
    #17
  18. L. Howard

    GS Guest

    > For clarity, it doesn't work on paragraphs! That means the cell can
    > only contain a single sentence.


    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
     
    GS, Apr 8, 2014
    #18
  19. L. Howard

    L. Howard Guest

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


    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
     
    L. Howard, Apr 9, 2014
    #19
  20. L. Howard

    GS Guest

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

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


    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
     
    GS, Apr 9, 2014
    #20
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Matt

    Changing from lower to upper case

    Matt, Jan 8, 2004, in forum: Excel Programming
    Replies:
    7
    Views:
    137
    David McRitchie
    Jan 8, 2004
  2. Replies:
    1
    Views:
    129
    Tom Ogilvy
    Jan 9, 2004
  3. Upper and Lower Bounds and Rnd

    , Jan 8, 2004, in forum: Excel Programming
    Replies:
    3
    Views:
    739
    Bob Phillips
    Jan 9, 2004
  4. Upper/Lower Bounds

    , Jan 8, 2004, in forum: Excel Programming
    Replies:
    0
    Views:
    147
  5. mike allen

    detect upper and lower case letters

    mike allen, Jun 5, 2004, in forum: Excel Programming
    Replies:
    3
    Views:
    158
    Cecilkumara Fernando
    Jun 5, 2004
  6. mtm4300 via OfficeKB.com

    Cases within Cases

    mtm4300 via OfficeKB.com, Feb 16, 2006, in forum: Excel Programming
    Replies:
    7
    Views:
    230
    Tom Ogilvy
    Feb 19, 2006
  7. VBA Noob

    Upper, Lower & Proper case

    VBA Noob, Jul 8, 2006, in forum: Excel Programming
    Replies:
    2
    Views:
    662
    VBA Noob
    Jul 8, 2006
  8. JS
    Replies:
    2
    Views:
    141
Loading...