Блог

Ниже приведена сводная таблица, созданная на основе следующего изображения данных. Моя проблема в том, что в случае 36 и 37 недель я не тратил на уход за собой, но я все равно хочу, чтобы прогнозируемые расходы на недели отражались в сводной таблице. Сводная таблица

Образец данных

 Public Sub Revamp()
Dim wb As Workbook
Dim ws As Worksheet
Dim count As Long
Dim i As Long
Dim trig As Boolean


Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet1")

'For loop that checks column WeekNum for the current week
For i = 1 To 52
    count = 0
    trig = False
    
'Groceries Loop
    Do While ws.Cells(9   count, 3).Value <> vbNullString
        
        If ws.Cells(9   count, 5).Value = i And _
            ws.Cells(9   count, 7).Value = "Groceries" And _
            ws.Cells(9   count, 2).Value = "2019" And _
            trig = False Then


            ws.Cells(9   count, 10).Interior.ColorIndex = 5
            ws.Cells(9   count, 10) = 12
            'ensures that the first occurrence of the If condition carries the Projected price for 
            'the week 
            trig = True
        End If
        
   
        
    count = count   1
    Loop

'Personal Care
    count = 0
    trig = False
    
    Do While ws.Cells(9   count, 3).Value <> vbNullString
        If ws.Cells(9   count, 5).Value = i And _
            ws.Cells(9   count, 7).Value = "Personal_Care" And _
            ws.Cells(9   count, 2).Value = "2019" And _
            trig = False Then


            ws.Cells(9   count, 10).Interior.ColorIndex = 7
            ws.Cells(9   count, 10) = 17
            trig = True
         End If
    count = count   1
    Loop

Next i


End Sub