Excel VBA 查找列"C"包含已知值的最后一行编号

Question

在Excel VBA中查找方法，以查找列"C"包含已知值的最后一行号。

Answer 1

这将在 C 列中找到最后一次出现的幸福

Sub SeekHappiness()
    Dim C As Range, where As Range, whatt As String
    whatt = "happiness"
    Set C = Range("C:C")
    Set where = C.Find(what:=whatt, after:=C(1), searchdirection:=xlPrevious)
    MsgBox where.Address(0, 0)
End Sub

要仅输出行号，请使用：

MsgBox Mid(where.Address(0, 0), 2)

要查找第一个匹配项，请执行以下操作：

Sub SeekHappiness()
    Dim C As Range, where As Range, whatt As String
    whatt = "happiness"
    Set C = Range("C:C")
    Set where = C.Find(what:=whatt, after:=C(1))
    MsgBox where.Address(0, 0)
End Sub

Answer 2

您可以遍历该列以查找值的最后一次出现。

Sub findLastRow()
  Dim searchValue As String
  Dim endRow As Integer
  Dim lastRowSearchValue As Integer
  searchValue = "testValue"        ''enter your search value
  With Worksheets("sheet1")        ''enter the name of your worksheet
      endRow = .Cells(Rows.Count, 3).End(xlUp).Row   
      For i = 1 To endRow
          If .Cells(i, 3) = searchValue Then
              lastRowSearchValue = i
          End If
      Next i
  End With
End Sub

只需将变量"searchValue"的值替换为您要查找的任何值（如果不是字符串，则可以更改变量的类型），Sub 会将搜索值出现的最后一行的索引存储在变量 "lastRowSearchValue" 中以供进一步使用。

Answer 3

Sub GetRo()
'Either select data or replace selection with your range
    Debug.Print Selection.Find(what:="mysring", searchdirection:=xlPrevious).Row
End Sub