有合并单元格时使用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屋!