Column To Upper() - In place

[Sky_B]

Is this possible? I'm trying to create a Macro command that will convert a column to upper() without creating a new column and using a =UPPER() formula in the new column.

So far I have this to select the column(s) that are currently active.

Selection.EntireColumn.Select

I would like to say something like

Selection.EntireColumn.Select
Selection.UPPER = Selection.EntireColumn.Select

Something along those lines anyway. Is this possible?

Thanks
Sky

 

[Jonmo1]

Welcome to the board.

Try something like

Sub test()
Dim MyArray As Variant, i As Long
With Application.Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    MyArray = .Value
    For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i, 1) = UCase(MyArray(i, 1))
    Next i
    .Value = MyArray
End With
End Sub

 

[Sky_B]

PERFECT!!!!!!

Thank you so much. This will save a ton of time.
Is there an "Answered" or something to say this post has been answered somehow?

thanks again,
Sky

 

[Rick Rothstein]

Welcome to the board.

Try something like

Sub test()
Dim MyArray As Variant, i As Long
With Application.Intersect(Selection.EntireColumn, ActiveSheet.UsedRange)
    MyArray = .Value
    For i = LBound(MyArray) To UBound(MyArray)
        MyArray(i, 1) = UCase(MyArray(i, 1))
    Next i
    .Value = MyArray
End With
End Sub

You can also do it without using a loop (nor restricting the selection to the UsedRange)...

[table="width: 500"]
[tr]
	[td]Sub MakeUpper()
  Selection = Evaluate(Replace("IF(@="""","""",UPPER(@))", "@", Selection.Address))
End Sub[/td]
[/tr]
[/table]

 

[Sky_B]

Could the same code be used to replace a string within each cell of a column?

Using the Replace to change say "The dog" to "The cat" in every occurrence in a column?
So not changing the content to UPPER but simply replacing without looping?

Thanks,
sky

 

[Rick Rothstein]

Could the same code be used to replace a string within each cell of a column?

Using the Replace to change say "The dog" to "The cat" in every occurrence in a column?
So not changing the content to UPPER but simply replacing without looping?

Is "The dog" the only text in the cell? What about letter casing... would "the dog" be replaced by "The cat" as well?

 

[Sky_B]

No, it's not the only text in the field.
My end game is to convert the column to UPPER and then replace (more accurately "remove") specific text from the cell.

Convert: New Member $5 Free Play
To: $5 FREE PLAY

Each cell in the row contains: New Member
The rest of the text in the cell is variable.

I was trying to learn it but with so many ways to do something I was getting lost.

Any thoughts on how to accomplish this?

Thanks,
Sky

 

[Sky_B]

I feel like I'm close with this but keep getting #VALUE when I run it. Any thoughts?

 Selection = Evaluate(Replace("IF(@="""","""",UPPER(REPLACE(@,1,11,"")))", "@", Selection.Address))

If i'm thinking correctly, this evaluates
IF @ (cell data) is blank then leave blank
ELSE convert to REPLACE @ (cell data) starting with the 1st character through the 11th character with nothing ("")
THEN convert to UPPER
FINALLY take @ and write it back into Selection.Address (cell)

Is this pretty close?

thanks,
sky

 

[Scott Huish]

Why not just remove the text and then use the formula Rick provided?

Sub test()
With Selection
    .Replace "New Member ", ""
    .Value = Evaluate(Replace("IF(@="""","""",UPPER(@))", "@", Selection.Address))
End With
End Sub

 

[Sky_B]

Works like a charm. Thank you.
I was using what Rick gave me and trying to adjust it slightly. The formula works when not used as VBA code but instead as an excel formula inside a cell.
Thank you guys so much for being active and nice.

Sky