VBA EXCEL范围语法 [英] VBA EXCEL Range syntax

问题描述

我不了解范围的语法.

为什么这样做:

For i = 1 To 10
    Range("A" & i & ":D" & i).Copy
Next

但这不起作用:

For i = 2 To lastRow
    num = WorksheetFunction.Match(Cells(i, 1), Range("A" & lastRow), 0)
 Next       

我为什么需要使用

For i = 2 To lastRow
    'num = WorksheetFunction.Match(Cells(i, 1), Range("A1:A" & lastRow), 0)
Next

A1:A是什么意思?为什么我不能使用

What A1:A mean? Why can't I use

Range("A" & lastRow), 0

推荐答案

语法没有问题，您的代码应该可以正常工作.
使用工作表函数(如Match，Vlookup和其他查找函数)的问题在于，如果找不到要搜索的值，则会引发错误.

There is nothing wrong with your syntax and your code should've work just fine.
The problem with using worksheet function like Match, Vlookup and other look up functions is that if the value being searched is not found, it throws up an error.

在您的情况下，您试图在一个单元格中搜索多个值.
假设您的lastrow是9.您的代码将从Cell(2,1)循环到Cell(9,1)，检查它是否在Range("A" & lastrow)或Range("A9")之内.

In your case, you are trying to search multiple values in just one cell.
So let us say your lastrow is 9. You're code will loop from Cell(2,1) to Cell(9,1) checking if it is within Range("A" & lastrow) or Range("A9").

如果从Cell(2,1)到Cell(9,1)的值与在Range("A9")中的值相同，则不会出现错误.

If your values from Cell(2,1) through Cell(9,1) is the same as your value in Range("A9"), you won't get an error.

现在，如果您使用Range("A1:A" & lastrow)，它肯定会起作用，因为您正在尝试将该范围内的每个元素与其自身进行匹配，并且肯定会找到匹配项.

Now, if you use Range("A1:A" & lastrow), it will surely work cause you are trying to match every element of that said range to itself and surely a match will be found.

WorksheetFunction.Match(Cells(2,1), Range("A1:A9")) 'will return 2
WorksheetFunction.Match(Cells(3,1), Range("A1:A9")) 'will return 3
'
'
'And so on if all elements are unique

使用Range("A9")或Range("A1:A9")都没有关系.
重要的是，如果找不到匹配项，则可以处理错误.
一种方法是像这样使用On Error Resume Next和On Error Goto 0:

It doesn't matter if you use Range("A9") or Range("A1:A9").
What matters is that you handle the error in case you did not find a match.
One way is to use On Error Resume Next and On Error Goto 0 like this:

Sub ject()
    Dim num As Variant
    Dim i As Long, lastrow As Long: lastrow = 9

    For i = 2 To lastrow
        On Error Resume Next
        num = WorksheetFunction.Match(Cells(i, 1), Range("A" & lastrow), 0)
        If Err.Number <> 0 Then num = "Not Found"
        On Error GoTo 0
        Debug.Print num
    Next
End Sub

另一种方法是像这样在WorksheetFunction.Match上使用Application.Match:

Another way is to use Application.Match over WorksheetFunction.Match like this:

Sub ject()
    Dim num As Variant
    Dim i As Long, lastrow As Long: lastrow = 9

    For i = 2 To lastrow
        num = Application.Match(Cells(i, 1), Range("A" & lastrow), 0)
        Debug.Print num
        'If Not IsError(num) Then Debug.Print num Else Debug.Print "Not Found"
    Next
End Sub

Application.Match的工作方式相同，但返回#N/A时不会出错.因此，您可以在Variant变量中分配它的值，并稍后在代码中使用它，而不会出现任何问题.更好的是，使用IsError测试来检查是否找不到上面注释行中所示的值.

Application.Match works the same way but it doesn't error out when it returns #N/A. So you can assign it's value in a Variant variable and use it later in the code without any problem. Better yet, use IsError test to check if a value is not found as seen above in the commented lines.

在以上两种情况下，我都使用了Variant类型的num变量.
主要原因是如果找不到匹配项，它将处理其他任何值.

In both cases above, I used a Variant type num variable.
Main reason is for it to handle any other value if in case no match is found.

对于范围语法，请不要混淆，这很简单.
请参考以下示例.

As for the Range Syntax, don't be confused, it is fairly simple.
Refer to below examples.

1. 单个单元格-全部指 A1

Cells(1,1) ' Using Cell property where you indicate row and column
Cells(1) ' Using cell property but using just the cell index
Range("A1") ' Omits the optional [Cell2] argument

不要与使用单元格索引混淆.就像您要对所有单元格从左到右，从上到下进行编号.

Don't be confused with using cell index. It is like you are numbering all cells from left to right, top to bottom.

Cells(16385) ' refer to A2

连续单元格的范围-均指A1:A10

Range of contiguous cell - All refer to A1:A10

Range("A1:A10") ' Classic
Range("A1", "A10") ' or below
Range(Cells(1, 1), Cells(10, 1))

上面的

使用相同的语法Range(Cell1,[Cell2])，其中第一个省略了optional自变量[Cell2].因此，下面的方法也适用:

Above uses the same syntax Range(Cell1,[Cell2]) wherein the first one, omits the optional argument [Cell2]. And because of that, below also works:

Range("A1:A5","A6:A10")
Range("A1", "A8:A10")
Range("A1:A2", "A10")

非连续单元格-均指A1，A3，A5，A7，A9

Non-Contiguous cells - All refer to A1, A3, A5, A7, A9

Range("A1,A3,A5,A7,A9") ' Classic

这篇关于VBA EXCEL范围语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！