如何根据多个条件查找excel中两行之间的匹配，包括在日期范围内拟合

This is busting my chops, I keep losing the rabbit while contemplating the logic necessary to complete this task. My VBA knowledge is virtually non-existent, so my apologies for not including what I've tried so far.

这正在破坏我的排骨，我在考虑完成这项任务所需的逻辑的同时一直在失去兔子。我的VBA知识几乎不存在，所以我抱歉不包括到目前为止我尝试过的内容。

I have two sheets with 5 columns. The first sheet contains schedules for future dates. The second sheet contains same day changes to that schedule. I would like to find instances when the same day change corresponds to a date, ID and color in the original schedule (meaning the the same day change was unnecessary or the schedule was faulty).

我有两张5列的床单。第一张表包含未来日期的时间表。第二张表包含该日程表的同一天更改。我希望找到当前更改对应于原始计划中的日期，ID和颜色的实例（意味着当天更改不必要或计划错误）。

Here are the two sheets Schedule Sheet

这是两张计划表

and Sane Day Sheet

和Sane日表

Ideally, I'd like to have rows 3 and 6 returned from the Same Day Sheet, or have a "1" in the F column.

理想情况下，我希望从同一天工作表中返回第3行和第6行，或者在F列中输入“1”。

I am totally lost and even a kick in the right direction would be a huge help.

我完全迷失了，即使向正确的方向踢一脚也会是一个巨大的帮助。

Thanks.

谢谢。

1 个解决方案

#1

This places a "1" in column F; it doesn't work with any error values - generates Type Mismatch errors

这在F列中放置一个“1”;它不适用于任何错误值 - 生成类型不匹配错误

---

Option Explicit

Public Sub FindConflicts()

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ur1 As Variant, ur2 As Variant, r1 As Long, r2 As Long
    Dim lb1 As Long, ub1 As Long, lb2 As Long, ub2 As Long

    Set ws1 = Worksheets("Schedule")
    Set ws2 = Worksheets("Same Day")

    ur1 = ws1.UsedRange:    lb1 = LBound(ur1):  ub1 = UBound(ur1)
    ur2 = ws2.UsedRange:    lb2 = LBound(ur2):  ub2 = UBound(ur2)

    ur2 = ws2.UsedRange.Resize(, ws2.UsedRange.Columns.Count + 1)
    For r2 = lb2 + 1 To ub2
        For r1 = lb1 + 1 To ub1
            If ur2(r2, 1) = ur1(r1, 1) Then         'compare columns 1
                If ur2(r2, 2) = ur1(r1, 2) Then     'compare columns 2

                    'compare 1st date in col 4 of ws2, if between col 4 and col 5 of ws1
                    'or      2nd date in col 5 of ws2, if between col 4 and col 5 of ws1
                    If (ur2(r2, 4) >= ur1(r1, 4) And ur2(r2, 4) <= ur1(r1, 5)) Or _
                       (ur2(r2, 5) >= ur1(r1, 4) And ur2(r2, 5) <= ur1(r1, 5)) Then

                            ur2(r2, 6) = 1

                    End If
                End If
            End If
        Next
    Next
    ws2.UsedRange.Resize(, ws2.UsedRange.Columns.Count + 1) = ur2
End Sub

---