excel - 搜索标题的最后一个实例

Question

我在工作表中有一个标题，都在第 1 行。我需要搜索每个特定标题的最后一个实例。请看下面我所看到的基本概念的截图。我需要宏来识别“狗”的最后一个实例，从左到右读取，或者“狗”的第一个实例从右到左读取，无论哪种方式都返回 I1 的地址。这同样适用于其余的标题，但是一旦给定一个正常工作的模板，我就可以处理它。

我在网上查了一下，我看不到任何有效的东西。

标签： excelvba

Answer 1

在这两种解决方案中，目标列都将存储为 a Range，这将允许您访问诸如Range.Column、Range.Row、Range.Address等属性。两个代码都经过测试并且工作正常

---

循环法

Option Explicit

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim LCol As Long, MyCell As Range, Found As Range

LCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

For Each MyCell In ws.Range(ws.Cells(1, 1), ws.Cells(1, LCol))
    If MyCell = "Dog" Then
        Set Found = MyCell
    End If
Next MyCell

If Not Found is Nothing Then MsgBox Found.Address(False, False)

End Sub

---

Range.Find方法

Option Explicit

Sub Test()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
Dim LCol As Long, Found As Range

LCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Set Found = ws.Range(ws.Cells(1, 1), ws.Cells(1, LCol)).Find("Dog", SearchDirection:=xlPrevious)

If Not Found Is Nothing Then MsgBox Found.Address(False, False)

End Sub