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 (MVP - Excel)


wrote in message
...
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