excel - 如何从 2 列报告重叠的单元格值

Question

我有 2 组时间序列（dd/mm/yyyy）可能在不同时期开始和结束。我想在另一个单元格中报告两列中都存在的所有重叠日期和与之相关的相关数据。

下图准确地解释了我拥有哪些数据以及我想用它做什么。

选择 2 列，创建变量并启动“foreach”循环，我不知道执行此类命令的代码结构。

Sub overlap()
Dim c As Range


For Each c In Selection
    If c.Value
Next c


End Sub

标签： excelvbaloops

Answer 1

未经测试，但这应该这样做：

    Option Explicit
    Sub Overlap()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim LastRow As Long, LastCol As Long
    Dim rng As Range, Rng2 As Range
    Dim Cell As Variant, Double As Variant

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Whatever")


    LastRow = ws.Cells(wsV.Rows.Count, "A").End(xlUp).row
    Lastcol = ws.Cells(1, .Columns.Count).End(xlToLeft).Column
    'Your entire range, here in column A    
    Rng = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol))
    ' Loop through each cell of the Range
    For Each Cell in Rng
        'Set a new range, from the beginning of the orginal one to your current row
        Set Rng2 = ws.Range(ws.Cells(1,1), ws.Cells(Cell.Row,1))
        With Rng2
        Set Double = .Find(Cell.Value, LookIn:=xlValues, Lookat:=xlWhole)
        ' If double is not nothing you've found the value of the current cell
        ' Meaning there's more than one
        If Not Double Is Nothing Then
          'Here you found a duplicate value - Do what you need to do
        End If  
      End With
    Next Cell     

End Sub