Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need to
use UCase on each individual cell?

Thanks,
Ken Loomis

Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need to
use UCase on each individual cell?

Thanks,
Ken Loomis

Thanks, Norman.

As usual, your suggestion worked.

I did have to change:

rCell.Value = UCase(myCell)

to:

rCell.Value = UCase(rCell)

but at least I am starting to be able to figure stuff like that out.

This sub requires the sheet "Enter Here" to be active.

Is there a way to do this without activating that sheet first?

Thanks,
Ken Loomis

"Norman Jones" wrote in message
...
Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need
to use UCase on each individual cell?

Thanks,
Ken Loomis

Hi Ken,

I changed variable names and missed one. Fortunately you spotted it!

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Thanks, Norman.

As usual, your suggestion worked.

I did have to change:

rCell.Value = UCase(myCell)

to:

rCell.Value = UCase(rCell)

but at least I am starting to be able to figure stuff like that out.

This sub requires the sheet "Enter Here" to be active.

Is there a way to do this without activating that sheet first?

Thanks,
Ken Loomis

"Norman Jones" wrote in message
...
Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need
to use UCase on each individual cell?

Thanks,
Ken Loomis

I think I figured out how to do that without activating the sheet.

I changed:

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

to:

Set rng = Intersect(Worksheets("Enter Here").Columns(2),
Worksheets("Enter Here").UsedRange)

And that works.

Thanks for all your help, Norman. I'm sure I'll need more.

Ken Loomis


"Norman Jones" wrote in message
...
Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need
to use UCase on each individual cell?

Thanks,
Ken Loomis

or even:

with worksheets("enter here")
Set rng = Intersect(.Columns(2), .UsedRange)
end with

Less typing and easier to read???



Ken Loomis wrote:

I think I figured out how to do that without activating the sheet.

I changed:

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

to:

Set rng = Intersect(Worksheets("Enter Here").Columns(2),
Worksheets("Enter Here").UsedRange)

And that works.

Thanks for all your help, Norman. I'm sure I'll need more.

Ken Loomis

"Norman Jones" wrote in message
...
Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need
to use UCase on each individual cell?

Thanks,
Ken Loomis





--

Dave Peterson

Hi Ken,

I think I figured out how to do that without activating the sheet.

I changed:

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

Actually,

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

does not activate the sheet; it merely refers to the sheet which is
currently active.

If you want to ensure that the macro only operates on a specific sheet, by
all means hardcode the sheet name, as you have done. Using ActiveSheet
provides some flexibility in that the macro can be used on any sheet (in any
workbook), providing that the sheet is the active sheet.


---
Regards,
Norman



"Ken Loomis" wrote in message
...
I think I figured out how to do that without activating the sheet.

I changed:

Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)

to:

Set rng = Intersect(Worksheets("Enter Here").Columns(2),
Worksheets("Enter Here").UsedRange)

And that works.

Thanks for all your help, Norman. I'm sure I'll need more.

Ken Loomis


"Norman Jones" wrote in message
...
Hi Ken,

Try:

Sub Tester()
Dim rng As Range, rCell As Range

On Error Resume Next
Set rng = Intersect(Columns(2), ActiveSheet.UsedRange)
On Error Resume Next
For Each rCell In rng.SpecialCells(xlCellTypeConstants, 2)

rCell.Value = UCase(myCell)
Next rCell
On Error GoTo 0

End Sub

---
Regards,
Norman



"Ken Loomis" wrote in message
...
Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need
to use UCase on each individual cell?

Thanks,
Ken Loomis

Ken Loomis wrote:

Is there a way to change all text in a column to upper case.

I tried this:

Worksheets("Enter Here").Range("B:B").Value = _
UCase(Worksheets("Enter Here").Range("B:B").Value)


But, as I am sure most of you here can tell, that did not work.

Is it because I am not accesing the column correctly, or do I just need to
use UCase on each individual cell?

Thanks,
Ken Loomis



Range("A:A").FormulaArray = _
"=UPPER('Enter Here'!" & Worksheets("Enter _
Here").Range("B:B").Address & ")"

Alan Beban