One single character to upper case

[stevewy]

I'm trying to do a simple macro to change the third character in a
selected cell to upper case, if the first two characters are "Mc".
It's for an address list where the names go Mcintosh, Mcardle, etc and
really need to be McIntosh, McArdle, with the third character
uppercase. I tried the macro below but it doesn't work. Am I using
UCase in the wrong way, or the MID function?

Sub McName_Adjustment()
Dim cell As Range
For Each cell In Selection.Cells
a = cell.Value
If Left(a, 2) = "Mc" Then
UCase (Mid(a, 3, 1))
End If
Next
End Sub

Thanks for any advice anyone can give.
Steve Wylie

 

[Rick Rothstein]

Try it this way (using Mid in both it Statement and its Function form)...

Sub McName_Adjustment()
Dim cell As Range
For Each cell In Selection.Cells
a = cell.Value
If Left(a, 2) = "Mc" Then
Mid(a, 3, 1) = UCase(Mid(a, 3, 1))
End If
Next
End Sub

 

[Rick Rothstein]

Of course, you have to assign it back to the cell. Add this just before the
End Sub statement...

cell.Value = a

By the way, the Mid Statement requires the first argument to be a variable,
so you can't use cell.Value in it directly.

 

[David]

You could try the enclosed in excell
=IF(LEFT(B6,2)="MC","Mc"&(UPPER(MID(B6,3,1)))&MID(B6,4,30),IF(LEFT(B6,3)="MaC","Mac"&(UPPER(MID(B6,4,1)))&MID(B6,5,30),(B6)))

where the original format name is in column B-
Not in a macro but does the job

 

[stevewy]

Thanks, Rick. Of course, I should have realised the amended text
would need putting back into the cell!

And thanks too, David, but I really did need this in a macro rather
than a formula.

Steve

 

[John_John]

Hi all!

You can use the power of Excel within your code.
For example:
If the range with the names contains only a single column,
try the macro below.

'-----------------8<----------------------------

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub Quick_McName_Adjustment()
Dim lngStart As Long
Dim strRef As String

lngStart = GetTickCount
Application.ScreenUpdating = False
strRef = Selection.Range("A1").Address(False, False)
With Selection.Cells.Offset(, 1)
.Insert xlToRight
.FormulaLocal = "=IF(LEFT(" & strRef & ";2)=""Mc"";" _
& "SUBSTITUTE(" & strRef & ";RIGHT(" & strRef & ";" _
& "LEN(" & strRef & ")-2);PROPER(RIGHT(" & strRef & ";" _
& "LEN(" & strRef & ")-2)));" & strRef & ")"
.Copy
Selection.PasteSpecial (xlPasteValues)
.Delete xlToLeft
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Debug.Print "Quick_McName_Adjustment: " & GetTickCount - lngStart & "
msec"
End Sub

'-----------------8<----------------------------

Enable the Immediate window to see the results.
In my system it needs 62 msec for the range "A1:A10000".

Sorry for my English.
I do not speak english very well. :-(

John

Ο χÏήστης "(e-mail address removed)" έγγÏαψε:

 

[John_John]

Correction!

My previous macro (Quick_McName_Adjustment) contains a problem.
Use the macro above :

'-----------------8<----------------------------

Private Declare Function GetTickCount Lib "kernel32" () As Long

Sub Quick_McName_Adjustment2()
Dim lngStart As Long
Dim strRef As String

lngStart = GetTickCount
Application.ScreenUpdating = False
strRef = Selection.Range("A1").Address(False, False)
Selection.Cells.Offset(, 1).Insert xlToRight

With Selection.Cells.Offset(, 1)
..FormulaLocal = "=IF(LEFT(" & strRef & ";2)=""Mc"";" _
& "SUBSTITUTE(" & strRef & ";RIGHT(" & strRef & ";" _
& "LEN(" & strRef & ")-2);PROPER(RIGHT(" & strRef & ";" _
& "LEN(" & strRef & ")-2)));" & strRef & ")"
..Copy
Selection.PasteSpecial (xlPasteValues)
..Delete xlToLeft
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Debug.Print "Quick_McName_Adjustment2: " _
& GetTickCount - lngStart & " msec"
End Sub

'-----------------8<----------------------------

Sorry for my carelessness.

 

[stevewy]

Thanks, John.

Steve