引用单元格地址并将其存储在范围对象中。

Question

我正在迭代一列，并希望捕获符合我正在搜索的文本字符串的单元格。当我试图设置符合搜索范围对象的条件的单元格的地址时，就会出现问题。行: Set testRng = i.Range.Adress给出了错误“错误的参数数或无效的属性分配”，我不知道这里的问题是什么？

这是我正在使用的全部代码：

Sub Tester()

Dim rng As Range
Dim testRng As Range
Dim i As Variant
Dim cmpr As String
Dim usrInputA As String
usrInputA = InputBox("Col 1 Criteria: ")
Set rng = Range("A2:A10")
For Each i In rng
    cmpr = i.Value
    If InStr(cmpr, usrInputA) Then
        If testRng Is Nothing Then
           Set testRng = i.Range.Address
        Else
            Set testRng = testRng & "," & i.Range.Address
        End If
    Else
        MsgBox "No hit"
    End If
    Next
End Sub

Answer 1

1. 您应该将i声明为Range (而不是Variant)
2. 使用Union将单元格集合组合在一起，而不是自己手动构建字符串
3. 切换范围设置的顺序。你只需要Set rngTest = i一次，所以我会把它放在底部，这样你就不用一直垃圾邮件了。

Option Explicit

Sub Tester()

Dim testRng As Range, i As Range
Dim usrInputA As String
Dim LR as Long

usrInputA = InputBox("Col 1 Criteria: ")
LR = Range("A" & Rows.Count).End(xlUp).Row

For Each i In Range("A2:A" & LR)
    If InStr(i, usrInputA) Then
        If Not testRng Is Nothing Then
           Set testRng = Union(testRng, i)
        Else
           Set testRng = i
        End If
    End If
Next i

If Not testRng is Nothing Then
    'Do what with testRng?
End If

End Sub