vba - 在工作表vba中查找特定列标题的第1次、第2次、第3次和第4次出现

Question

我正在尝试查找工作表中出现的特定列标题。例如标题名称付款日期，需要在工作表中查找付款日期的出现次数，例如第一次出现的单元格地址、第二次出现的单元格地址、第三次出现的单元格地址和第四次出现的单元格地址。任何建议将不胜感激。下面的代码无法正常工作

Sub find()
d = "Payout Date"
Set r = ThisWorkbook.ActiveSheet.UsedRange.find(d)
Debug.Print r.Address
Set r = ThisWorkbook.ActiveSheet.UsedRange.FindNext(d)
Debug.Print r.Address
Set r = ThisWorkbook.ActiveSheet.UsedRange.FindNext(d)
Debug.Print r.Address
Set r = ThisWorkbook.ActiveSheet.UsedRange.FindNext(d)
Debug.Print r.Address
End Sub

Answer 1

以下内容可能会有所帮助。

Sub Demo()
    Dim ws As Worksheet
    Dim i As Long, cnt As Long
    Dim rng As Range, rng2 As Range
    Dim cellFound As Range

    Set ws = ThisWorkbook.Sheets("Sheet1")  'change Sheet1 to your data sheet
    Set rng = ws.Range("1:1") 'assuming headers are in row 1 else change 1 to row number with headers
    Set rng2 = rng(1, Columns.Count)
    cnt = 3              'number of occurrences to find
    i = 1
    With rng
    Set cellFound = .find(what:="ID", After:=rng2, LookIn:=xlValues)
        If Not cellFound Is Nothing Then
           firstAddress = cellFound.Address
           Do
               Debug.Print "Occurrence " & i & " : " & cellFound.Address
               i = i + 1
               If i > cnt Then: Exit Do
               Set cellFound = .FindNext(cellFound)
           Loop While cellFound.Address <> firstAddress
         End If
      End With
End Sub

查看图片以供引用。