☰

日期改变时移动到下一行数据

tag-icon • tag-icon microsoft-excel
日期改变时移动到下一行数据

我有以下代码...

子测试()

Workbooks("MAY10-Key Indicator Daily Reportcopy.xls").Sheets("Input").Activate
Set c = Range("B15:B45")
For Each cell In c
    If cell.Value = Range("I5").Value Then
        Workbooks("McKinney Daily Census Template NOV 10.xls").Sheets("McKinney").Range("C15:I15").Copy
        cell.Offset(0, 37).PasteSpecial
    End If
    Next
Application.CutCopyMode = False

If Cell.Value = Date Then
    Range(Cells(Cell.Row + 1, 3).Address, Cells(Cell.Row + 1, 9).Address).Select
    Cells(Cell.Row + 1, 3).Activate
   Exit For
End If

Next Cell
End Sub

在 WB（McKinney Daily Census Template...）中，我有一列日期与另一个 WB 匹配。当日期更改时，我需要 WB（McKinney...）下降到下一个行范围。IE

11/01/10 - C15:I15

11/02/10 - C16:I16

11/03/10 - C17:I17

我需要向代码中添加什么才能实现此目的。谢谢

答案1

此代码已更新，以满足评论中指出的需求。

Sub test()
    Dim MayCell As Range, MayC As Range
    Dim McCell As Range, McC As Range
    Dim McK As Workbook, MAY10 As Workbook
    Dim ChkDate As Variant

    Set MAY10 = Workbooks("MAY10-Key Indicator Daily Reportcopy.xls")
    'may need to change, if your MAY10 file name changes
    Set McK = Workbooks("McKinney Daily Census Template NOV 10.xls")
    'this may need to change for each month/year

    MAY10.Activate
    MAY10.Sheets("Input").Activate
    ChkDate = Range("I5").Value
    Set MayC = Range("B15:B45")
    For Each MayCell In MayC
        If MayCell.Value = ChkDate Then
            McK.Activate
            McK.Sheets("McKinney").Activate
            Set McC = Range("B15:B45")
            For Each McCell In McC
                If McCell.Value = ChkDate Then
                    Range(Cells(McCell.Row, 3).Address, Cells(McCell.Row, 9).Address).Copy
                    Exit For
                End If
            Next McCell
            MAY10.Activate
            MayCell.Offset(0, 37).PasteSpecial
            Exit For
        End If
    Next MayCell
    Application.CutCopyMode = False
End Sub

相关内容