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

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

Answer 2

使用Evaluate将列大写

Sub UCaseColumnTEST()
Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
UCaseColumn ws, "A1", "A20"
End Sub
Sub UCaseColumn( _
ByVal ws As Worksheet, _
ByVal FirstCellAddress As String, _
ByVal LastCellAddress As String)
With ws.Range(FirstCellAddress, LastCellAddress)
.Value = ws.Evaluate("UPPER(" & .Address & ")")
End With
End Sub