Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lowercase Macro (2k)

    I've never actually written a macro, but i have some VB experience. I was wondering how i would go about converting all cell's in a column to lowercase. However i'm not really familiar with the syntax involved in referencing cells in excel. i figure i have to do something along the lines of this:

    Dim x As Integer
    Dim y As String

    For x = 4 To 35
    y = A$x 'the cell column/row?
    A$x = LCase(y)
    Next

    End

    any help is greatly appreciated
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lowercase Macro (2k)

    You can refer to a cell in several ways. One possibility is in the form Range("C37"), and another is in the form Cells(37, 3). In your macro, you could use Range("A" & x) or Cells(x, 1).

    If necessary, you can also specify the worksheet:

    Worksheets("MySheet").Range("C37")

    or even the workbook and worksheet:

    Workbooks("MyWorkbook.xls").Worksheets("MySheet"). Range("C37")

    If you omit the workbook, the active workbook is assumed, and if you also omit the worksheet, the active sheet is assumed.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lowercase Macro (2k)

    aha, exactly what i was looking for, thanks hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lowercase Macro (2k)

    You could make a macro to switchcase as well, then assign to menu or toolbar. I found this one while floating around the lounge oneday and like the way it behaves.

    Sub Switchcase()
    Dim Cell As Range
    For Each Cell In Selection
    Select Case Cell
    Case LCase(Cell) 'It's lowercase
    Cell = UCase(Cell)
    Case UCase(Cell) 'It's uppercase
    Cell = Application.Proper(Cell)
    Case Else 'It's neither upper nor lower
    Cell = LCase(Cell)
    End Select
    Next

    End Sub

    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lowercase Macro (2k)

    I would change the switching order:

    <pre>Case LCase(Cell) 'It's lowercase
    Cell = Application.Proper(Cell)
    Case UCase(Cell) 'It's uppercase
    Cell = LCase(Cell)
    Case Else 'It's neither upper nor lower
    Cell = UCase(Cell)</pre>


    In your code a single letter will never be switched once it goes to upper case (it will always switch to proper which keeps it upper case).

    In the code above it will toggle from upper -lower - upper - lower, etc.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lowercase Macro (2k)

    Your right! Never tried it on a single letter. Most times it's requested for Name fields in my class...and no one ever has had a name with just one character up to this point...but until you pointed this out, hadn't had the occasion to try it on a single character...
    Should one need to use this on a field that holds a single character, your code works much better. Thank you very much. [img]/forums/images/smilies/smile.gif[/img]
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •