Macro to perform a formula function

Posted by Rick on September 29, 2001 5:47 AM

Either I'm not seeing this clearly or just not thinking, but is it possible to create a Macro so that it will perform the formula function of taking text and converting it to either upper case or lower case? When I try to do this the formula calls for particular cells to be entered. What I want to end up with is a Macro that can perform this function regardless of what I select in the worksheet. My end goal is to create to buttons on the toolbar with one taking text and formatting it for upper case and another thats the text and formats it in lower case. Am I missing something here? If you can assist me, please email at ipshina@aol.com

Thanks,
Rick

Posted by ian on September 29, 2001 8:03 AM

Sub TextLowerCase()
Dim rng As Range, cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
For Each cell In rng
If cell.HasFormula = False Then
cell.Value = LCase(cell.Value)
End If
Next cell
End Sub

Sub TextUpperCase()
Dim rng As Range, cell As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
For Each cell In rng
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next cell
End Sub

Note : These macros ignore cells that have formulas, since otherwise they would be converted to values only.