在合并的单元格中寻找价值 [英] Find value in merged cell

问题描述

我在合并单元格上搜索值时遇到问题.第9行和第10行合并，标记为灰色.现在，我要搜索文本"TOTAL"并转到其最后一行的值是"2812".有什么方法可以做到不解开单元格吗?同样，单词"TOTAL"也不是唯一的.在第二十列中也有"TOTAL"，但我想选择第一列中包含单词的词.

I have a problem on searching value on a merged cell. Row 9 and Row 10 are merged marked as the grey ones. Now, I want to search a text "TOTAL" and go to its lastrow value which is "2812". Is there a any way to do that without unmerging the cells? Also, the word "TOTAL" is not unique. There's also "TOTAL" on the other 20th column but I want to select the first column who have the word.

推荐答案

以下假设标题位于活动表的第1行

Below assumes headings are in row 1 of the activesheet

Sub t()
Dim rng As Range
With ActiveSheet
    Set rng = .Range("A9:" & .Range("ZZ9").End(xlToRight).Address)
    col = Application.WorksheetFunction.Match("TOTAL", rng, 0)
    lastrow = .Cells(1000000, col).End(xlUp).Row
    Debug.Print Cells(lastrow, col).Value

End With
End Sub

要查找第二次出现的情况，请复制代码并将起始范围从"A9"更改为.Cells(9，col + 1).解决该问题将在下一列开始新的范围

To find a second occurrence, replicate the code and change the start range from "A9" to .Cells(9, col + 1).Address this will start the new range on the next column

Sub t()
Dim rng As Range
With ActiveSheet
    Set rng = .Range("A9:" & .Range("ZZ9").End(xlToRight).Address)
    col = Application.WorksheetFunction.Match("TOTAL", rng, 0)
    lastrow = .Cells(1000000, col).End(xlUp).Row
    Debug.Print Cells(lastrow, col).Value

    Set rng = .Range(.Cells(9, col + 1).Address & ":" & .Range("ZZ9").End(xlToRight).Address)
    col2 = Application.WorksheetFunction.Match("TOTAL", rng, 0)
    Debug.Print Cells(lastrow, col + col2).Value

End With
End Sub

这篇关于在合并的单元格中寻找价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！