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

  • 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

Eloise T

Active Member
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
 
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?
 
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!
 
Hi, Eloise T!
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?
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!
 
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)
 

Attachments

  • Chandoo - TOGGLECASE - CHANGE CASE.xlsm
    17.9 KB · Views: 10
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!
 
Hi, Eloise T!
All workbooks have one worksheet and with the same columns, E:F? YES
Or a search for "Y/N" should be performed on title cells? NOT NECESSARY
In this case, titles in row 1? ROW 5
Regards!
PS: Still with 2010 and very happy!
 
Hi !
Please see attached file.
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:
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 !
 
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:
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 …
 
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 …
Outstanding! Worked like a charm! Knowing Alt + F8 to run code was the key.
 
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 …
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?
 
Is Demo meant to stop "looking" once it hits a blank E/F cell?
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:
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
 
Is Demo meant to stop "looking" once it hits a blank E/F cell?
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 :
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 …

 
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
 

Attachments

  • Chandoo - TOGGLECASE - CHANGE CASE.xlsm
    16.8 KB · Views: 3
Last edited:
Just loop each worksheet.

Code:
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
 
Just loop each worksheet.

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


THANK YOU!
 
Oh one thing. You may want to replace Sheet1 in below section with ws.
Code:
With ws.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)
 
Just loop each worksheet.

Code:
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
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?
 
Oh one thing. You may want to replace Sheet1 in below section with ws.
Code:
With ws.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)


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:
Oh one thing. You may want to replace Sheet1 in below section with ws.
Code:
With ws.[E3:F3].Resize(Sheet1.Cells(Rows.Count, 1).End(xlUp).Row - 2)
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?
 
Back
Top