将整个范围转换为大写而不遍历所有单元格 [英] Convert an entire range to uppercase without looping through all the cells

问题描述

现在我正在使用以下代码将股票代码列表从小写字母转换为大写字母:

right now I'm using the following code to convert a list of ticker symbols from lowercase to upper case letters:

Dim Tickers As String
Dim n As Integer
For n = 2 To Last
    Tickers = UCase(W.Cells(n, 1).Value)
    W.Cells(n, 1).Value = Tickers
Next n

有没有一种方法可以在一行中转换整个范围?类似:

Is there a method I can use to convert the whole range in one line? something like:

Range("A1:A20").convertouppercasesomehow

推荐答案

有没有一种方法可以在一行中转换整个范围?

Is there a method I can use to convert the whole range in one line?

是的，您可以在不循环的情况下进行转换.试试这个

Yes you can convert without looping. Try this

Sub Sample()
    [A1:A20] = [INDEX(UPPER(A1:A20),)]
End Sub

或者，使用可变范围，试试这个:

Alternatively, using a variable range, try this:

Sub Sample()
    Dim rng As Range
    Set rng = Range("A1:A20")
    rng = Evaluate("index(upper(" & rng.Address & "),)")
End Sub

根据你的例子

W.Range("A1:A20") = [index(upper(A1:A20),)]

说明

[A1:A20] = [INDEX(UPPER(A1:A20),)] 有两部分

第 1 部分

如上所示，[A1:A20] 只不过是一种写Range("A1:A20")

As shown above, [A1:A20] is nothing but just a short way of writing Range("A1:A20")

第 2 部分

[INDEX(UPPER(A1:A20),)]

Index 和 Upper 是工作表函数.所以你可以使用 Application.Worksheetfunction.Index() 但因为我们没有像 Application.Worksheetfunction.UPPER() 这样的 UPPER 等价物>，我们只能写成[cell] = [UPPER(cell)]

Index and Upper are worksheet functions. So you can use Application.Worksheetfunction.Index() but since we don't have an equivalent of UPPER like Application.Worksheetfunction.UPPER(), we can only write it as [cell] = [UPPER(cell)]

现在通过那一行，我们指示 VBA 返回一个数组，这就是 INDEX 发挥作用的地方.(正如我们所知，INDEX 函数有两种形式:数组形式和引用形式.)通过不指定数组的行或列，我们只是让 Excel 知道我们想要整个数组.(在 VBA 帮助中也提到)所以基本上我们正在做的是将 [A1:A20] 中的每个单元格转换为大写

Now with that line we are instructing VBA to return an array and this is where INDEX comes into play. (As we are aware, there are two forms of the INDEX function: the array form and the reference form.) By not specifying a row or a column of the array, we are simply letting Excel know that we want the whole array. (Mentioned in VBA help as well) So basically what we are doing is converting each cell in [A1:A20] into uppercase

这篇关于将整个范围转换为大写而不遍历所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！