比较日期，并将数据放在日期下

• 请验证 SheetC1.Range（“C2”） 和 SheetDest.Cells（2， i） 的数据类型。确定如何准确比较它们的值至关重要。

• 如果您的文件与代码中的假设不一致，请提供具体详细信息。这将有助于进行任何必要的调整或澄清。

Option Explicit

Sub Demo()
    'loop through columns
    Dim i As Long
    Dim LastCol As Long
    Dim DataDate As String ' **
    Set SheetC1 = ThisWorkbook.Sheets("C1 EV Application") ' Modify as needed
    Set SheetDest = ThisWorkbook.Sheets("C0 EV (Historie)")
    ' If C2 is formatted as "general" and shows `Jan.2024`
    ' It means C2 is a String instead of Date
    DataDate = SheetC1.Range("C2").Value
    For i = 21 To 100 'from column "S" to 100 (fixed range, 100/12months=8.3 years)
        ' Assumes SheetDest.Cells(2, i) is formatted as Date
        With SheetDest.Cells(2, i)
            If IsDate(.Value) Then
                If StrComp(Format(.Value, "MMM.yyyy"), DataDate, vbTextCompare) = 0 Then
                    Cells(3, i).Value = SheetC1.Range("AD27").Value
                    Exit For
                End If
            End If
        End With
    Next i
End Sub

VBA 查找：“Application.Match”日期使用CLng

Sub Historie_speichern()

    Const SRC_NAME As String = "C1 EV Application"
    Const SRC_DATE_CELL As String = "C2"
    Const SRC_KPI_CELL As String = "AD27"
    Const DST_NAME As String = "C0 EV (Historie)"
    Const DST_DATES_LEFT_CELL As String = "S2"
    Const DST_KPI_ROW As Long = 3

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim sws As Worksheet: Set sws = wb.Sheets(SRC_NAME)
    Dim sDate As Date: sDate = sws.Range(SRC_DATE_CELL).Value
    Dim sKPI As Variant: sKPI = sws.Range(SRC_KPI_CELL).Value
    
    Dim dws As Worksheet: Set dws = wb.Sheets(DST_NAME)

    With dws
        .Range("B2:E20").Value = sws.Range("C4:F22").Value
        .Range("F2:G20").Value = sws.Range("N4:O22").Value
        .Range("H2:I20").Value = sws.Range("Q4:R22").Value
        .Range("J2:K20").Value = sws.Range("T4:U22").Value
        .Range("L2:M20").Value = sws.Range("W4:X22").Value
        .Range("N2:O20").Value = sws.Range("Z4:AA22").Value
    End With
    
    Dim drrg As Range
    
    With dws.Range(DST_DATES_LEFT_CELL)
        Dim LastCol As Long:
        LastCol = dws.Cells(.Row, dws.Columns.Count).End(xlToLeft).Column
        Set drrg = .Resize(, LastCol - .Column + 1)
    End With
        
    Dim cIndex As Variant: cIndex = Application.Match(CLng(sDate), drrg, 0)
        
    If IsNumeric(cIndex) Then
        drrg.Cells(cIndex).EntireColumn.Cells(DST_KPI_ROW).Value = sKPI
    End If

End Sub