将整个范围转换为大写,而不循环遍历所有单元格 [英] Convert an entire range to uppercase without looping through all the cells
问题描述
现在我正在使用以下代码将小写字母列表转换成大写字母:
对于n = 2到Last
Tickers = UCase(W.Cells(n,1).Value)
W.Cells(n ,1).Value = Tickers
Next n
有没有一种我可以使用的方法将整个范围转换成一行?如下:
Range(A1:A20)。convertouppercasesomehow
pre>
解决方案
有没有一种方法可以用来将整个范围转换成一行? / p>
是的,您可以无循环转换。尝试这个
Sub Sample()
[A1:A20] = [INDEX(UPPER(A1:A20) )]
End Sub
根据你的例子
W.Range(A1:A20)= [index(upper(A1:A20),)]
/ pre>
说明
[A1:A20] = [INDEX(UPPER(A1:A20),)]
第1部分
如上所示,
[A1:A20]
只不过是短暂的写作方式范围(A1:A20)
第2部分 p>
[INDEX(UPPER(A1:A20),)]
索引
和上部
是工作表函数。因此,您可以使用Application.Worksheetfunction.Index()
,但由于我们没有相当于UPPER
code> Applicaiton.Worksheetfunction.UPPER(),我们只能将其写为[cell] = [UPPER(cell)]
/ p>
现在用这一行我们指示
VBA
返回数组,这是INDEX
发挥作用。 (我们知道,有两种形式的INDEX
函数:数组形式和引用形式。)通过不指定一行或一列数组,我们只是让Excel知道我们想要整个数组。 (在VBA帮助中也提到)所以基本上我们正在做的是将[A1:A20]
中的每个单元格转换为大写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
As per your example
W.Range("A1:A20") = [index(upper(A1:A20),)]
Explanation
There are two parts to
[A1:A20] = [INDEX(UPPER(A1:A20),)]
PART 1
As shown above,
[A1:A20]
is nothing but just a short way of writingRange("A1:A20")
PART 2
[INDEX(UPPER(A1:A20),)]
Index
andUpper
are worksheet functions. So you can useApplication.Worksheetfunction.Index()
but since we don't have an equivalent ofUPPER
likeApplicaiton.Worksheetfunction.UPPER()
, we can only write it as[cell] = [UPPER(cell)]
Now with that line we are instructing
VBA
to return an array and this is whereINDEX
comes into play. (As we are aware, there are two forms of theINDEX
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屋!