Capitalize Dynamic Range in VBA

Question

(Coding rookie posting first ever question so please pardon my mistakes)

I'm trying to learn simple methods of data validation. I read another post similar to what I'm doing: convert-entire-range-to-uppercase, but it doesn't work when I change the range to fit my needs. Couldn't find anything else that addressed this.

I have an Excel column named "Block" that appears in different locations in different workbooks, and I need to capitalize any letters that occur in that column. I think the code works as intended until the final line, which results in "#NAME?" filling the whole range.

This is what I have so far:

Dim LastColumn As Long
Dim LastRow As Long
Dim BlockColumn As Long
Dim BlockRange As Range

    'defines LastColumn, LastRow & BlockColumn
    LastColumn = Cells.Find(What:="*", After:=Range("a1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    LastRow = Cells.Find(What:="*", After:=Range("a1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    BlockColumn = Cells.Find(What:="Block", After:=Range("a1"), LookAt:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column

    'capitalizes any text in BlockColumn
    Set BlockRange = Range(Cells(2, BlockColumn), Cells(LastRow, BlockColumn))
    BlockRange = [UPPER(BlockRange)]

Aside from wondering where I made a mistake, I'm sure I've over-complicated this. Could someone show me a way to rethink or simplify it? I was also wondering the general pros and cons to accomplishing a task like this via looping (as opposed to this method), but not sure if this is the place to ask that...

Answer 1

[] is shorthand for Evaluate and does not accept variables.

You will need to actually use Evaluate.

You also need INDEX to not overwrite the entire range with the first value.

blockRange.value = blockRange.Parent.Evaluate("INDEX(UPPER(" & blockRange.Address & "),)")