VBA，查找单元格地址并用作另一个查找的范围

Question

我现在正竭尽全力想弄清楚发生了什么，我试图在VBA中使用两个独立的搜索函数来查找单元格范围的开始和结束，然后将其设置为另一个搜索范围。这可能是一件非常简单的事情，但我一辈子都想不出来，我在下面包含了我当前的代码。

Truecheck是一个全局变量，用于存储要在spreadhseet中搜索的名称。我想要定义的范围的开始和结束将具有相同的名称，因此truecheck应该对两者都有效。

有人知道怎么做吗？

目前，它抛出了一个对象所需的错误，突出显示了我设置"Firstrow=Range…"的部分。我也认为这里有不止一个问题，然而

编辑：Potatoes.value和Textboxinput.value是用户表单上的文本框

Private Sub optionselect()
    Dim LastLocation As String
    Dim FirstLocation As String
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim SearchVal As String
    FirstLocation = Range("B:B").Find(truecheck,_
    LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlByRows)
    LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues,_ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)
    FirstRow = Range(FirstLocation).Row
    LastRow = Range(LastLocation).Row
    Potatoes.Value = Application.WorksheetFunction.VLookup(LengthInputText.Value,_
    Range(Cells(FirstRow, 8), Cells(LastRow, 8)), 6, False)
End Sub

Answer 1

FirstLocation和LastLocation are both defined as字符串yet you are assigning them to a范围object in the way the statement is written, as the查找method returns the cell (or范围`object）。

实现这一点的最简单方法是将Address属性添加到调用中。

FirstLocation = Range("B:B").Find(truecheck,_
    LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlByRows).Address
LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues,_ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious).Address

然而，这也可以做到：

Dim FirstLocation as Range, LastLocation as Range
Set FirstLocation = Range("B:B").Find(truecheck,_
    LookIn:=xlValues,LookAt:=xlWhole, SearchOrder:=xlByRows)
Set LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues,_ 
    LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)
FirstRow = FirstLocation.Row
LastRow = LastLocation.Row

Answer 2

它看起来像Range的返回类型。Find是一个范围而不是字符串。把你的代码改成这样，然后试试：

Private Sub optionselect()
Dim LastLocation As Range
Dim FirstLocation As Range
Dim FirstRow As Long
Dim LastRow As Long
Dim SearchVal As String
Set FirstLocation = Range("B:B").Find("It", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
Set LastLocation = Range("B:B").Find(truecheck, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, searchdirection:=xlPrevious)
FirstRow = FirstLocation.Row
LastRow = LastLocation.Row
Potatoes.Value = Application.WorksheetFunction.VLookup(LengthInputText.Value,_
Range(Cells(FirstRow, 8), Cells(LastRow, 8)), 6, False)
End Sub

FYI：https://msdn.microsoft.com/en-us/library/office/ff839746.aspx