UCase Column BH in a Sheet

[Corey]

I have used the Ucase line of code before to format a Textbox or a single cell,
but how can i get it to work on the entirte column BH in a worksheet ?

I tried:
Range("BH:BH").value = Ucase(Range("BH:BH").value)

but i get a type missmatch error


Corey....

 

[Guest]

Sub upper()
Set r = Intersect(Range("BH:BH"), ActiveSheet.UsedRange)
For Each rr In r
rr.Value = UCase(rr.Value)
Next
End Sub

 

[Guest]

There is no "one line" solution. You need to iterate thorugh the constants in
the column and do each on individually. Something like this...

Sub UpperCase()
Dim rngConstants As Range
Dim rng As Range

On Error Resume Next
Set rngConstants = Columns("BH").SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If rngConstants Is Nothing Then
MsgBox "Sorry... Nothing to upper case."
Else
For Each rng In rngConstants
rng.Value = UCase(rng.Value)
Next rng
End If
End Sub

 

[Corey]

Thanks Perfect


Sub upper()
Set r = Intersect(Range("BH:BH"), ActiveSheet.UsedRange)
For Each rr In r
rr.Value = UCase(rr.Value)
Next
End Sub

 

[Guest]

This code works fine so long as there are no formulas in column BH. If there
are it converts the formulas into values that are upper cased. That is
probably an unintended side effect (although that depends on what the OP
wants). Up to you Corey...

 

[Corey]

Thanks Jim.

There will be NO Formula's, ONLY Text values in the Column.

Corey....

This code works fine so long as there are no formulas in column BH. If there
are it converts the formulas into values that are upper cased. That is
probably an unintended side effect (although that depends on what the OP
wants). Up to you Corey...

 

[Guest]

As a general rule I always avoid using procedure that could have unintended
side effects. If I had a nickel for every time that a spreadsheet needed to
be modified in a way that I had not envisioned when it was first created...
that being the case while I am sure there are no formulas in that column now
how about down the road??? Do you want to debug magic disappearing formulas
or use code that will just never overwrite your formulas?

The code that I posted can not overwrite your formulas and it is a bit more
efficient than the code posted by Gary''s Students (no disrespect to Gary''s
code). Normally I would not recommend my code over other code that works (I
have often recommended someone elses code over mine when it was superior)
but in this case I would be inclined to use the code that I posted.