1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CHANGE CASE function

Discussion in 'VBA Macros' started by Eloise T, Apr 25, 2017.

  1. Eloise T

    Eloise T Active Member

    Messages:
    436
    I have been pestering Microsoft Excel to put CHANGE CASE (exactly like what is in Word) in MS Excel...so far to no avail. Obviously, I have been pestering the MS We-Don't-Care-What-Your-Suggestion-May-Be---Go-Away dept.

    My problem: I have spreadsheets sent to me weekly in which I need to change y and n to Y and N before I forward them.

    The following VBA was sent to me by an unknown friend.
    I'm able to install the TOGGLECASE function but, ...

    1) I don't know how to use it.
    2) I don't think it will do any better than the already existing UPPER, PROPER, or LOWER functions which means I would have to create special columns to pull the data from the existing columns, and then Copy and Paste Special the corrected data back to the original columns, correct?

    Function TOGGLECASE(Text As String) As String

    Dim x As Long
    Dim Character As String
    Dim NewTextArray As Variant

    ReDim NewTextArray(1 To Len(Text))

    For x = 1 To Len(Text)
    Character = Mid$(Text, x, 1)

    Select Case Character
    Case "A" To "Z"
    NewTextArray(x) = LCase$(Character)
    Case "a" To "z"
    NewTextArray(x) = UCase$(Character)
    Case Else
    NewTextArray(x) = Character
    End Select
    Next x

    TOGGLECASE = Join(NewTextArray, vbNullString)

    End Function
    Chirag R Raval likes this.
  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    You can either use the UDF in a module or as worksheet function.

    But I'm not sure what you are trying to accomplish here. Can you upload sample of your data and expected output?
    ThrottleWorks likes this.
  3. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688
    Hi, Eloise T!

    In general: Would you please elaborate and upload a sample file indicating worksheets and columns to be processed?

    In particular: Functions are to be installed in modules .bas or in the worksheet object page (name of the worksheet) or in the workbook object page. Then depending the scope they can be used in formulas or in VBA code. I think that this last is your case.

    Regards!
    ThrottleWorks likes this.
  4. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688
    Hi, Eloise T!
    Regarding 2), absolutely correct about ToggleCase vs Upper/Lower/Proper.
    But in your case, if the only thing you have to do with the received workbooks is changing 'y' and 'n' for 'Y' and 'N', I'd go for a macro in a workbook in the folder where the processed files should be stored, run it to act on a temp folder with the incoming files, and then move the processed workbooks from the temp to the final folder.
    If any more actions to be done with those workbooks, please give us a detailed list.
    Regards!
    ThrottleWorks likes this.
  5. Eloise T

    Eloise T Active Member

    Messages:
    436
    Please see attached file. Thanks in advance for your help.


    ET
    ---------------------------------------------------------------------------------------
    "Perseverance is failing 19 times requesting an upgrade from the boss and succeeding anyway using Excel 2007."- Eloise T.

    (Still using Excel 2007)

    Attached Files:

  6. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688
    Hi, Eloise T!
    All workbooks have one worksheet and with the same columns, E:F?
    Or a search for "Y/N" should be performed on title cells?
    In this case, titles in row 1?
    Regards!
    PS: Still with 2010 and very happy!
    ThrottleWorks likes this.
  7. Eloise T

    Eloise T Active Member

    Messages:
    436
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    Hi !
    According to your attachment,
    see this demonstration from Excel 2003 version :DD
    (test it first with your attachment, mod it after according to real files) :​
    Code (vb):
    Sub Demo()
        With Sheet1.[E3:F3].Resize(Sheet1.[A1].CurrentRegion.Rows.Count - 2)
            .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
        End With
    End Sub
    Do you like it ? So thanks to click on bottom right Like !
    Chirag R Raval likes this.
  9. Eloise T

    Eloise T Active Member

    Messages:
    436
    Marc,
    ALT + F11 to install. How do I implement it?

    Sub Demo()
    With Sheet1.[E3:F3].Resize(Sheet1.[A1].CurrentRegion.Rows.Count - 2)
    .Value = .Parent.Evaluate(Replace("IF($E$3:$E$100 & $F$3:$F$100>"""",UPPER($E$3:$E$100 & $F$3:$F$100),"""")", "$E$3:$E$100 & $F$3:$F$100", .Address))
    End With
    End Sub

    ET
    ---------------------------------------------------------------------------------
    "Perseverance is failing 19 times requesting an upgrade from the boss and succeeding anyway using Excel 2007."- Eloise T.

    (Still using Excel 2007)
    Last edited: Apr 26, 2017
  10. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    In VBE side of your attachment,
    just paste my code without any mod to a module
    or to ThisWorkbook module.

    As the address within formula is automatic,
    you must not change this codeline even with real workbook !

    If you need a mod with your real data, just mod With codeline
    for the beginning cells and the minus value for number of rows …

    On worksheet side Alt + F8 to run code.

    The With codeline on a smart worksheet may be
    With Sheet1.UsedRange.Offset(1).Columns("E:F")
    but as it depends on real layout as should have any attachment !

    If you stay on a dark side, just attach a workbook with real layout …
    Eloise T likes this.
  11. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688
    Hi, Marc L!
    I love when someone else does the dirty job.
    So grateful that gonna invite you a Carlsberg :)
    Regards!
    ThrottleWorks and Marc L like this.
  12. Eloise T

    Eloise T Active Member

    Messages:
    436
    Outstanding! Worked like a charm! Knowing Alt + F8 to run code was the key.
  13. Eloise T

    Eloise T Active Member

    Messages:
    436
    Please see attachment. I ran installed and ran the "Demo." It replaced all the lower case with upper case until it got to cell E19. This was an error on my part as I initially missed E19 which should have been capped. by me. Is Demo meant to stop "looking" once it hits a blank E/F cell?
  14. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,688
    Hi, Eloise T!
    Missing attachment or same as above file?
    Regards!
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Marc's code uses .CurrentRegion to determine last row. By default it will stop when there is empty row(s).

    If you have empty row(s) intermixed in your data range...
    Change it to something like.
    Code (vb):
    Sub Demo()
        With Sheet1.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)
            .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
        End With
    End Sub
    ThrottleWorks and Chirag R Raval like this.
  16. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    No as written 'cause of your bad attachment
    which not respects your real data worksheet …
    As CurrentRegion from A1 cell stops at the first empty row
    aka nothing from A to H columns …

    The reason why I wrote :
  17. Marc L

    Marc L Excel Ninja

    Messages:
    3,043
    I got so scared seeing the code of initial post ! :p
    SirJB7 likes this.
  18. Eloise T

    Eloise T Active Member

    Messages:
    436
    I have modified the spreadsheet so that it has 19 identical tabs. (Still need only columns E and F starting with row 3.)

    I need the VBA to ignore the first tab and function for the remaining 19.

    Preferably the VBA can be Run once and hit all 19 tabs. If that's not a good idea nor possible, it should be able to be run on each tab individually (2-19).


    Thank you!

    CURRENT VBA:
    Sub Demo()
    With Sheet1.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)
    .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
    End With
    End Sub

    Attached Files:

    Last edited: Jun 14, 2017
    Chirag R Raval likes this.
  19. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Just loop each worksheet.

    Code (vb):
    Sub Demo()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "TheSheetToExclude" Then 'Change it to your sheet name or you can use sheet index# instead of Name.
           With ws.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)
                .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
            End With
        End If
    Next
    End Sub
    Chirag R Raval and Eloise T like this.
  20. Eloise T

    Eloise T Active Member

    Messages:
    436

    THANK YOU!
  21. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Oh one thing. You may want to replace Sheet1 in below section with ws.
    Code (vb):
    With ws.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)
  22. Eloise T

    Eloise T Active Member

    Messages:
    436
    Since Tab #1 is the sheet to exclude, I would change "TheSheetToExclude" to either "1" or "Formula Info"
    Correct?

    Also, I assume this will go beyond Tab 19, correct?
    Chirag R Raval likes this.
  23. Eloise T

    Eloise T Active Member

    Messages:
    436

    This is what I've got. The first tab, Formula Info, will be skipped and the VBA will continue until it runs out of tabs, correct?



    Sub ChangeCase()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Formula Info" Then
    'Change it to your sheet name or you can use sheet index# instead of Name.
    With ws.[E3:F3].Resize(ws.Cells(Rows.Count, 1).End(xlUp).Row - 2)
    .Value = .Parent.Evaluate(Replace("IF(#>"""",UPPER(#),"""")", "#", .Address))
    End With
    End If
    Next
    End Sub
    Last edited: Jun 14, 2017
    Chirag R Raval likes this.
  24. Eloise T

    Eloise T Active Member

    Messages:
    436
    One more thing. It appears to work correctly on the data except when it's done it gives:

    upload_2017-6-14_14-9-16.png

    Is there a way to avoid this?
  25. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Upload sample workbook where it produces that error. I've never seen it before.

    P.S. Please use CODE tag.
    ThrottleWorks likes this.

Share This Page