Search code examples
vbarangecelluppercase

VBA Convert Range to Uppercase

I would like to convert a Range (from "I5" to the last Cell) to Uppercase in an Excel Table I calculate the last Cell and try to put it into my code but it always says "object required" What am i doing wrong? This is part of my code.

Dim rng As String

rng = Range("I5").SpecialCells(xlCellTypeLastCell).Address(RowAbsolute:=False, ColumnAbsolute:=False)

["I5:" & rng] = [INDEX(UPPER("I5:" & rng),)]
Solution

  • The square brackets is another universe. They expect a constant and they obey to the formula syntax, not VBA.

    With a fixed range, you would go with = [INDEX(UPPER(I5:I20), )] (note the absence of " around the address). If you try to put rng there, it will be understood as a name of an Excel function (that does not exist).

    If you want to keep the square brackets approach (I assume to avoid an explicit For loop), then try

    Range("I5:" & rng).Value = Application.Evaluate("INDEX(UPPER(I5:" & rng & "), )")