VBA:我一直得到一个与对象变量没有正确设置相关的错误

Question

我试图设置这个简单的代码，但无论我做什么，我总是得到一个错误'object required'的行:

Set foundCell = searchRange。找到(:= searchValue:原地= xlValues)

源代码如下:

Option Explicit
'I'm going to let the program search for the date in the pre-filled date -> select the entire row & column below -> move it down by 1 row -> insert the date above the moved pre-filled date -> fill other details
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim submittedDate As Date
Dim searchValue As Variant
Dim searchRange As Variant
Dim foundCell As Range

Set ws = Sheet1 'setting it up so that I'm dealing with Sheet1
ws.Range("A7:A37").NumberFormat = "dd-mmm" 'setting the number format of the values in A7:A37 as dd-mmm
submittedDate = Me.TextBox1.Value 'setting the input value as a variable
searchValue = submittedDate
searchRange = ws.Range("A7:A37")
Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues)

If Not foundCell Is Nothing Then
MsgBox "Value found!"
Else
MsgBox "Value not found..."
End If

End Sub

任何想法/帮助将是感激和提前感谢你!

我似乎想不出任何解决方案，因为所有的变量都声明了…

Answer 1

Range类的Find()方法需要调用Range对象，而

searchRange = ws.Range("A7:A37")

将导致searchRange作为存储单元格&;A7: a37 &;值的数组，因为Range对象的默认属性是Value(就好像您已经编码了searchRange.Value = ws.Range("A7:A37"))。

而为了有一个Range对象，你必须使用Set关键字

Set searchRange = ws.Range("A7:A37")

也就是说，您最好将searchRange声明为Range类型

Dim searchRange As Range

会在编译阶段为Set searchRange = ws.Range("A7:A37")引发错误，而不是在运行时遇到的错误

最后，您可以省略submittedDate变量的声明和使用，直接使用searchValue变量

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim searchValue As Variant
Dim searchRange As Range
Dim foundCell As Range

Set ws = Sheet1 'setting it up so that I'm dealing with Sheet1
ws.Range("A7:A37").NumberFormat = "dd-mmm" 'setting the number format of the values in A7:A37 as dd-mmm
searchValue = Me.TextBox1.Value 'setting the input value as a variable
Set searchRange = ws.Range("A7:A37")
Set foundCell = searchRange.Find(What:=searchValue, LookIn:=xlValues)

If Not foundCell Is Nothing Then
MsgBox "Value found!"
Else
MsgBox "Value not found..."
End If

End Sub

其更精简的版本如下:

Private Sub CommandButton1_Click()
Dim foundCell As Range
With Sheet1.Range("A7:A37") ' reference the needed range
.NumberFormat = "dd-mmm" 'setting the number format of the values in referenced range as dd-mmm
Set foundCell = .Find(What:=Me.TextBox1.Value, LookIn:=xlValues) 'search for the TextBox1 value in the referenced range values
End With
If Not foundCell Is Nothing Then
MsgBox "Value found!"
Else
MsgBox "Value not found..."
End If

End Sub