UCase Column BH in a Sheet
 

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....

UCase Column BH in a Sheet
 

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

--
Gary''s Student - gsnu200731


"Corey" wrote:

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....

UCase Column BH in a Sheet
 

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
--
HTH...

Jim Thomlinson


"Corey" wrote:

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....

UCase Column BH in a Sheet
 

Thanks Perfect


"Gary''s Student" wrote in message
...
Sub upper()
Set r = Intersect(Range("BH:BH"), ActiveSheet.UsedRange)
For Each rr In r
rr.Value = UCase(rr.Value)
Next
End Sub

--
Gary''s Student - gsnu200731


"Corey" wrote:

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....

UCase Column BH in a Sheet
 

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...

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200731


"Corey" wrote:

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....

UCase Column BH in a Sheet
 

Thanks Jim.

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

Corey....

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

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200731


"Corey" wrote:

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....

UCase Column BH in a Sheet
 

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.
--
HTH...

Jim Thomlinson


"Corey" wrote:

Thanks Jim.

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

Corey....

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

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200731


"Corey" wrote:

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....