excel VBA:我一直收到与对象变量设置不正确相关的错误

ut6juiuv  于 2023-01-27  发布在  其他
关注(0)|答案(1)|浏览(102)

我尝试设置这个简单的代码,但无论我做什么,我总是得到一个错误'object required'的行:
设置查找单元格=搜索范围。查找(内容:=搜索值,查找范围:= xl值)
我的源代码如下:

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

任何想法/帮助将不胜感激,并提前感谢你!
我似乎想不出任何解决方案,因为所有的变量都是声明的...

goqiplq2

goqiplq21#

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

相关问题