excel - 为什么在 VBA 中查找时出现错误？

Question

我正在尝试使用 VBA 在范围内搜索字符串。我拼凑了一些代码，但在“如果不是”行中不断收到 1004 错误:

Sub test1()

Dim wb As Workbook
Dim ws As Worksheet
Dim found_range As Range
Dim search_range As Range

Set wb = Workbooks("D1")
Set ws = wb.Sheets("Master data")

Set search_range = ws.Cells(147, 1).EntireRow

If Not Range(search_range).Find("Test") Is Nothing Then
    'match found
    Set found_range = Range(search_range).Find("Test")
    Debug.Print found_range.Column
Else
    MsgBox "No match found"
    'no match found
End If

End Sub

关于我为什么会收到错误的任何想法？

Answer 1

我对双 .Find 有点困惑
如果您的 Range.Find方法已经返回 Range对象一次，无需设置两次。

还有search_range已经是 Range对象，因此需要尝试将其封装在另一个 Range() 中目的。

In fact it's the reason, you are getting the error, because it expects a string inside the type-casted Range(<string>)

As @MathieuGuindon correctly pointed out:

It is the cause of the error, but the reason is because the unqualified Range call is a child object of whatever the ActiveSheet is, and you can't do Sheet1.Range(Sheet2.Cells(1, 1), Sheet2.Cells(1,> 10)) - error 1004 is thrown in OP's code because ws isn't the ActiveSheet; qualifying the Range call with ws would have fixed the error too... but yeah Range(someRange) is definitely redundant.

Sub test1()

  Dim wb As Workbook
  Dim ws As Worksheet
  Dim found_range As Range
  Dim search_range As Range

  Set wb = Workbooks("D1")
  Set ws = wb.Sheets("Master data")

  Set search_range = ws.Cells(147, 1).EntireRow
  Set found_range = search_range.Find("Test")

  If Not found_range Is Nothing Then
      Debug.Print found_range.Address
  Else
      MsgBox "No match found"
  End if

End Sub