Excel VBA range.find起作用 [英] Excel VBA range.find acting up

问题描述

我尝试使用range.find来查找列中的值，并从下一列返回匹配的值。

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

我记录了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.

这是我有的

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

错误I现在得到的是：

对象变量或未设置块变量

Object variable or With block variable not set

任何想法为什么现在正在发生？

Any idea why this is now happening?

我可以在range.find（）上找到的所有资源看起来像我在做正确的...

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

干杯 - David

Cheers - David

推荐答案

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

更多 .Find HERE

这篇关于Excel VBA range.find起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！