有合并单元格时使用find方法，vba excel [英] Use find method when there are merged cells, vba excel

问题描述

当搜索范围内有合并单元格时，我有一个关于在 Excel VBA 中使用 .find 方法的问题.代码如下:

I have a question about using the .find method in Excel VBA when there is a merged cell in the searching range. Here is the code:

Dim SearchCell As Range
Set SearchCell = ActiveSheet.Range("A:A").Find(What:="Open RMA*", LookIn:=xlValues)

运行代码后SearchCell为空，我猜是因为A26:K26被合并为一个单元格，而值为Open RMA"；不过我不想取消合并.

SearchCell is empty after running the code, I guess because A26:K26 is merged as one cell while the value is "Open RMA"; I don't want to unmerge it though.

推荐答案

我认为这是有问题的，除非您扩展范围以包括合并单元格的范围，例如将 A:A 更改为 A:D.

I think this is problematic unless you extend the range to include the merged cell's range, changing A:A to A:D, for example.

另一种方法是:

Dim SearchCell As Range

If Not Application.IsNA(Application.Match("Open RMA*", Range("A:A"), 0)) Then
    Set SearchCell = Cells(Application.Match("Open RMA*", Range("A:A"), 0), 1)
    MsgBox "Found at " & SearchCell.Address
End If

不过，它需要更好的错误检查，以防找不到该值.添加如下:

It requires some better error checking, though, in case the value is not found. Added like this:

If Not IsError(Application.Match("Open RMA*", Range("A:A"), 0)) Then
    Set SearchCell = Cells(Application.Match("Open RMA*", Range("A:A"), 0), 1)
    MsgBox "Found at " & SearchCell.Address
End If

这篇关于有合并单元格时使用find方法，vba excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！