Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
4.2k views
in Technique[技术] by (71.8m points)

vba - How to organize information based on a range of dates

I have an information with a data frame (ex: 01.01.2021 - 27.01.2021), and I need to classify it in groups (ex.: W1, W2, W3, W4), then in each group classify in subgroups (Day 1, day 2, day 3, day 4 and day 5), then count it.

Ps: As there are 7 (monday to sunday) different dates for 5 groups (week numbers), and the data from day 5 and 6 of each week should be combined

Sample

'
' Organize Macro
' Arranges data from Excel
'

Workbooks.Open Filename:= _
        "C:UsersGXG9623OneDrive - International Flavors & Fragrances Inc-DocumentsSAPSAP GUITeste.xls"
 
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Range("B9").Select
    Rows("3:3").Select
    Rows("2:3").Select
    Range("A3").Activate
    Range("2:3,1:1").Select
    Range("A1").Activate
    Selection.Delete Shift:=xlUp
    Columns("A:D").Select
    Selection.Delete Shift:=xlToLeft
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("A1:B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Teste!R1C1:R815C35", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=6
    Sheets("Sheet1").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Pstng Date"), "Count of Pstng Date", xlCount
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Pstng Date")
        .Orientation = xlRowField
        .Position = 1
    End With
End Sub

Sub Present()
'
' Present Macro
' Fits the data into KPI AP Brazil

Workbooks.Open Filename:= _
        "S:ND_P2PLatin America13 - BrazilKPI - AP Brazil.xlsx"

    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""04.01.2021"")"
    Range("H5").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""05.01.2021"")"
    Range("I5").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""06.01.2021"")"
    Range("J5").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""07.01.2021"")"
    Range("K5").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""08.01.2021"")"
    Range("G6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""11.01.2021"")"
    Range("H6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""12.01.2021"")"
    Range("I6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""13.01.2021"")"
    Range("J6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""14.01.2021"")"
    Range("K6").Select
    ActiveCell.FormulaR1C1 = _
        "=GETPIVOTDATA(""Pstng Date"",[Teste.xls]Sheet1!R3C1,""Pstng Date"",""15.01.2021"")"
    Range("K7").Select
    
    ThisWorkbook.Save
    
End Sub```

The goal is to reference the data based on the dates. I.E.: 5 invoices processed on Week 1, day 4.




与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)
等待大神解答

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...