Excel VBA range.find代理

时间：2023-01-14 23:54:03

Im trying to use range.find to look up a value within a column, and return the matching value from the next column.

我试图使用range.find在列中查找值,并从下一列返回匹配值。

I recorded the find() using the macro recorder, which seemed to work fine for a while, but for some reason it's now giving me an error. As far as I can tell I haven't changed anything that should affect this bit of code.

我使用宏录制器录制了find(),它似乎工作了一段时间,但由于某种原因它现在给了我一个错误。据我所知,我没有改变任何会影响这段代码的事情。

This is what I have

这就是我所拥有的

Public Function look_up_id(id, table)
    Worksheets(table).Activate
    Cells.Find(What:=id, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    look_up_id = ActiveCell.Offset(0, 1).Value
End Function

The error I'm now getting is:

我现在得到的错误是:

Object variable or With block variable not set

对象变量或未设置块变量

Any idea why this is now happening?

知道为什么现在这样吗?

All the resources I can find on range.find() look like I'm doing it right...

我在range.find()上找到的所有资源看起来都像我做得对...

Cheers - David

干杯 - 大卫

2 个解决方案

#1

4  

Try this

Public Function look_up_id(id, table) As Variant
    Dim ws As Worksheet
    Dim aCell As Range

    look_up_id = "Not Found"

    Set ws = ThisWorkbook.Sheets(table)

    With ws
        Set aCell = .Cells.Find(What:=id, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)

        If Not aCell Is Nothing Then _
        look_up_id = aCell.Offset(, 1).Value
    End With
End Function

More on .Find HERE

更多关于。在这里

#2

4  

Try to use this code instead (when Find doesn't find anything, it returns Nothing and then you tried to do sth like this Nothing.Activate, and this triggers an error):

尝试使用此代码(当Find找不到任何内容时,它会返回Nothing,然后你尝试做这样的Nothing.Activate,这会触发错误):

Dim res As Range

Set res = Worksheets(table).Cells.Find(What:=id, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
If Not res Is Nothing Then
    look_up_id = res.Offset(0, 1).Value
End If

#1

4  

Try this

Public Function look_up_id(id, table) As Variant
    Dim ws As Worksheet
    Dim aCell As Range

    look_up_id = "Not Found"

    Set ws = ThisWorkbook.Sheets(table)

    With ws
        Set aCell = .Cells.Find(What:=id, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)

        If Not aCell Is Nothing Then _
        look_up_id = aCell.Offset(, 1).Value
    End With
End Function

More on .Find HERE

更多关于。在这里

#2

4  

Try to use this code instead (when Find doesn't find anything, it returns Nothing and then you tried to do sth like this Nothing.Activate, and this triggers an error):

尝试使用此代码(当Find找不到任何内容时,它会返回Nothing,然后你尝试做这样的Nothing.Activate,这会触发错误):

Dim res As Range

Set res = Worksheets(table).Cells.Find(What:=id, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False)
If Not res Is Nothing Then
    look_up_id = res.Offset(0, 1).Value
End If
标签：

相关文章