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" & ")")

--


Gary


"Myles" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=489318
>

[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

Myles wrote:
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile: http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=489318

[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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=489318
>

[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(A1:D10) 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)


"Myles" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=489318
>