excel - 将列转换为大写的动态范围

Question

Sub Trim(sh,FirstCell,LastCell)
    Sheets(sh).select
    Range(FirstCell,LastCell) = [index(Upper(FirstCell,LastCell),)]
End Sub

我想做这样的事情来让它充满活力。通过其他应用程序调用此宏，在该应用程序中我为工作表名称的第一个单元格和范围的最后一个单元格提供输入。

Answer 1

不能使用[]对于变量，它需要一个字符串。范围也已经在变量中有工作表并且不需要添加它:

Sub MyTrim(rng as Range)    
    rng.value = rng.Parent.Evaluate("index(Upper(" & rng.adress & "),)")
End Sub

或者使用 UCase:

Sub MyTrim(rng as Range)
    If rng.Cells.Count = 1 Then
        rng = UCase(rng)
        Exit Sub
    End If
    Dim RngArray() as Variant
    RngArray = rng

    Dim i as Long
    For i = 1 to Ubound(RngArray,1)
        Dim j as Long
        For j = 1 to Ubound(RngArray, 2)
            RngArray(i,j) = UCase(RngArray(i,j))
        Next j
    Next i
    rng = rngarray
End Sub