澳大利亚项目VBA部分简略代码

Option Explicit

Public Sub setDateCluster()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
    Dim rowCount1 As Integer, rowCount2 As Integer, clusterNum As Integer, i As Integer, j As Integer, rowNum As Integer
    Dim maxDate As Date, max1 As Date, max2 As Date
    Dim arr1 As Variant, arr2 As Variant
    Set s1 = Worksheets("Sheet1")
    Set s2 = Worksheets("Sheet2")
    Set s3 = Worksheets("Sheet3")
    rowCount1 = s1.Range("A1").CurrentRegion.Rows.Count
    rowCount2 = s2.Range("A1").CurrentRegion.Rows.Count
    arr1 = Array("cluster21", "cluster22")
    arr2 = Array("cluster31", "cluster32")
    s1.Range(s1.Cells(2, 1), s1.Cells(rowCount1, 1)).NumberFormatLocal = "yyyy-m-d"
    s2.Range(s2.Cells(2, 1), s2.Cells(rowCount2, 1)).NumberFormatLocal = "yyyy-m-d"
    
    max1 = Application.WorksheetFunction.Max(s1.Range(s1.Cells(2, 1), s1.Cells(rowCount1, 1)))
    max2 = Application.WorksheetFunction.Max(s2.Range(s2.Cells(2, 1), s2.Cells(rowCount2, 1)))
    
    maxDate = Application.WorksheetFunction.Max(max1, max2)
    minDate = maxDate - 27
    clusterNum = Application.Worksheets("Sheet4").ComboBox.ListCount
    rowNum = 2
    s3.Range(s3.Cells(2, 1), s3.Cells(s3.Range("A1").CurrentRegion.Rows.Count, 26)).Clear
    For i = 1 To clusterNum - 1
        For j = 1 To 28
            s3.Range(s3.Cells(rowNum, 2), s3.Cells(rowNum, 2)).Value = Application.Worksheets("Sheet4").ComboBox.List(i)
            s3.Range(s3.Cells(rowNum, 1), s3.Cells(rowNum, 1)).Value = maxDate - 28 + j
            rowNum = rowNum + 1
        Next j
    Next i
    
    Set s1 = Nothing
    Set s2 = Nothing
    Set s3 = Nothing
    
End Sub

Sub insertCombox()
    Dim rowCount As Integer
    Dim s4 As Worksheet
    Dim i As Integer
    Set s4 = Worksheets("Sheet4")
    rowCount = s4.Range("A1").CurrentRegion.Rows.Count
    Application.Worksheets("Sheet4").ComboBox.Clear
    Application.Worksheets("Sheet4").ComboBox.AddItem ""
    For i = 1 To rowCount
        Application.Worksheets("Sheet4").ComboBox.AddItem s4.Range(s4.Cells(i, 1), s4.Cells(i, 1)).Value
    Next i
    
    Set s4 = Nothing
    
End Sub

Sub getStartEndRow()
    Dim minDate As Date, maxDate As Date
    Dim i As Integer, j As Integer
    minDate = Application.Worksheets("Sheet3").Range("A2").Value
    maxDate = minDate + 27
    Debug.Print minDate
    Debug.Print maxDate
    
    For i = 1 To Application.Worksheets("Sheet4").ComboBox.ListCount
        For j = 1 To 28
            
        Next j
    Next i
    
End Sub