Feb 25 2021 06:46 PM
Changing a letter to its opposite case. Input is 'A', output should be 'a'. And vice versa. Thanks.
Feb 25 2021 09:15 PM
Since you want to automate the exercise so that your need is VBA macro.
Use this VBA code as Standard module.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Choice As String
Choice = Range("F1").Value
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Application.EnableEvents = False
If Choice = "Upper" Then
Target = StrConv(Target, vbUpperCase)
End If
If Choice = "Lower" Then
Target = StrConv(Target, vbLowerCase)
End If
If Choice = "Proper" Then
Target = StrConv(Target, vbProperCase)
End If
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub
How it works:
N.B.
In VBA code Range("A1:A10")) is editable & you many set as your need.
Feb 25 2021 11:22 PM
If you're looking for a formula, you could try:
=IF(CODE(B1)>=97,UPPER(B1),LOWER(B1))
But, as Rajesh stated, if you want it to happen automatically, then you will need vba. Should it apply to the entire worksheet, or just particular cells? Another way I've seen it done is to do a bitwise xor:
Private Sub Worksheet_Change(ByVal Target As Range)
Const ascDiff As Long = &H20
Dim rangeToChange As Range
Dim cell As Range
Set rangeToChange = Me.Range("A1:A10")
If Intersect(Target, rangeToChange) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
For Each cell In Intersect(Target, rangeToChange).Cells
If (Not Target.HasFormula) And (Len(Target.Value) = 1) Then
Target = Chr(Asc(Target.Value) Xor ascDiff)
End If
Next cell
ExitProc:
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub
Feb 26 2021 03:02 PM