ucase 'data type mismatch'

[DKY]

I'm trying to make my selection uppercase by doing something like this.

Selection.Value = ucase(Selection.Value)

and it keeps giving me a data type mismatch error. What am I doing
wrong here?

 

[Jim May]

Gotta run. But existing code only works on a single-cell narrow your
highlighted-area and retry.
HTH

 

[Gary Keramidas]

try this

Option Explicit
Dim cell As Range
Sub test()
For Each cell In Selection
cell.Value = UCase(cell.Value)
Next
End Sub

 

[Bob Phillips]

For Each cell In Selection
cell.Value = UCase(cell.Value)
Next cell



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[DKY]

I think that the code only goes through one cell at a time, I'll pos
the code below

Code
-------------------
Option Explicit

Sub HEADER_MAKER()
Dim CELL
Application.ScreenUpdating = False
For Each CELL In Selection
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
CELL.Borders(xlDiagonalDown).LineStyle = xlNone
CELL.Borders(xlDiagonalUp).LineStyle = xlNone
With CELL.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With CELL.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With CELL.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With CELL.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Times New Roman"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

' START REPLACE
Selection.Replace What:=" ", Replacement:="_", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' END REPLACE
' START REPLACE
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' END REPLACE

'Selection.Value = upper(Selection.Value)

Selection.EntireColumn.AutoFit

Next CELL
End Sub

 

[Guest]

If selection.TypeName = "Range" then
if selection.Count = 1 then
if not iserror(selection) then
selection.Value = ucase(selection.Value)
end if
end if
end if

 

[DKY]

That's perfect!!

Not Selection.value but CELL.value, that makes sense.

 

[DKY]

That's perfect!!

Not Selection.value but CELL.value, that makes sense.

 

[Bob Phillips]

Yes, but cell is just an object name that I chose (it kinda makes sense). It
could as easily be

For Each sausage In Selection
sausage.Value = UCase(sausage.Value)
Next sausage

that variable is just used to refer to each item in the collection (of
selected cells here)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Jim May]

Bob:
Great !! -- I now grasp the full concept of "For Each ..." through
this illustration -- keep such 'em coming.
Jim May