Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Testing text case (Excel xp)

    I want to examine certain cells to insure the text such as "Nov-03" is changed to NOV-03. I thought one could employ all of excel's functions within VBA, so why do I get a Sub or Function not defined? Thanks.
    Sub Caps()
    Dim content As String

    Range("J15").Select
    content = ActiveCell.Formula
    ActiveCell.Formula = UPPER(content)

    End Sub

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

    Re: Testing text case (Excel xp)

    VBA, worksheet functions are methods of the WorksheetFunction object. But UPPER is not available as a method, because VBA has the UCase function that does the same.

    You don't need to select a cell to change it:

    Range("J15") = UCase(Range("J15"))

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing text case (Excel xp)

    Thanks alot. I went back into the Object Browser and saw the Ucase method. Should have looked harder.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Testing text case (Excel xp)

    Hi Torquemada,

    Just in case you're trying to reformat a date - that approach won't work unless you convert them to text strings first. Excel has its own ideas about how date values should be formatted.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Testing text case (Excel xp)

    Where can I get some references on converting and manipulating dates in VBA? I wish that search function were back on. I'd pay good money to have it back. I have an entry date in one cell, in date format. If another cell as the value "accrual" then I need the cell discussed in this thread (reversal month) to read NOV-03 in text, for instance, for a 10/31/03 entry date. This is the way the Nitpick 5000 system requires it.

  6. #6
    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: Testing text case (Excel xp)

    in VB:
    Range("J15") = ucase(format(range("J15"),"mmm-yy"))

    FYI, the equivalent function in excel would be :
    =UPPER(TEXT(J15,"mmm-yy"))

    Though you would have to place it in another cell (other than J15).

    In either case, the value ceases to be a date. It is now text.

    Steve

Posting Permissions

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