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