using EVALUATE to change text to uppercase

[Myles]

If Range("a1:d10") has lower case text, CodeA successfully converts to
UPPER CASE in Range("e1:h10") using WorkSheetFunction FormulArray. I
would expect EVALUATE to achieve the same result using CodeB. Can
someone tweak it to work?

CODE A.
Sub UpperCaseArrayFmla()
Lcol = Cells.SpecialCells(xlCellTypeLastCell).Column
rws = ActiveSheet.UsedRange.Rows.Count
cols = ActiveSheet.UsedRange.Columns.Count

Set rng = Cells(1, Lcol + 1).Resize(rws, cols)
rng.FormulaArray = "=UPPER(" & "a1:d10" & ")"
End Sub

CODE B
Sub EvaluateUpperCaseFmla()
Dim a
Lcol = Cells.SpecialCells(xlCellTypeLastCell).Column
rws = ActiveSheet.UsedRange.Rows.Count
cols = ActiveSheet.UsedRange.Columns.Count

Set rng = Cells(1, Lcol + 1).Resize(rws, cols)
'ReDim a(rws, cols)
a = Evaluate("(UPPER" & "a1:d10" & ")")
rng.Value = a
End Sub

PS: The alternate and perhaps superior way is looping through the range
using vba UCASE

 

[Gary Keramidas]

not sure what you're trying to do, but the evaluate is done like this, but
it probably isn't the result you want

a = Application.Evaluate("=UPPER(" & "a1:d10" & ")")

 

[MrScience]

Hi Myles,

I wonder if it would help to use a Do While type of approach to loop
through all the cells in a column and convert them to upper case. This
approach would only work if the rows are contiguous but here's
something I use all the time to convert text to upper case, lower case
or proper case, etc.

Sub changeToUpperCase()
Dim firstCell As Variant
set firstCell = Range("A2") 'reference whichever start cell you want
Do While Not IsEmpty(firstCell)
Set nextCell = firstCell.Offset(1,0) 'refer to the cell under the start
cell
'put any test conditions here such as . . .
'If firstCell. HasFormula = False Then
'firstCell.Value = UCase(firstCell)

'if you have no conditions to apply then just convert the existing
value in the cell to UCase .

firstCell.Value = UCase(firstCell)
Set firstCell = nextCell 'here we're refering to the cell underneath
for the next pass
Loop 'go back to the top of the loop
MsgBox "File Done"
End Sub

 

[Bob Phillips]

That won't work, UPPER is not an array formula, so it only evaluates the
first cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Myles]

Thanks Bob. Your answer seems plausible as indeed only the first cell
is correctly evaluated. It would appear at first sight that EVALUATE
has the autosense to treat a formula as Array if the context points to
that. In my example (Code B), the selected range is populated with the
Array formula but all cells bear the evaluation of the first cell.


TIA

 

[Bob Phillips]

That is exactly the same as it would be if you select a block of cells in a
worksheet and did =UPPER(A110) as an array formula. They all get the first
cell upshifted, which kinda gives the game away.

--

HTH

RP
(remove nothere from the email address if mailing direct)