PDA

View Full Version : Trying to make a loop to shorten my code



Loranga
04-04-2007, 01:38 AM
Aloha, (sorry for my bad english)

I have a sheet where I can controll a pivot table with two buttons on the sheet. I can choose wich week I want to show in the table with a spinbutton. I also have two option buttons that let me choose if I want to see a singel week or accumulate a number of weeks in the table.

I need some help making some form of loop or something that will shorten the code below. (since I would like to have 52 weeks the code will be huge if I do it like below).

This is my code so far, I have only 6 weeks.



Sub Makro1()

Application.ScreenUpdating = False
Dim Singleweek_or_Accumulated As Integer
Dim Week_number As Integer

Singleweek_or_Accumulated = Range("J27")
' if J27 is = 1 the I will show singel week in the table
' if J27 is = 2 the I will show accumulated weeks in the table

Week_number = Range("J29")
' J29 is the cell where i select the week number

If Singleweek_or_Accumulated = 1 Then
' singel week scenario
With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = True
End With

With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
Select Case Week_number

Case Is = 10
.PivotItems("10").Visible = True
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 11
.PivotItems("10").Visible = False
.PivotItems("11").Visible = True
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 12
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = True
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 13
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = True
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 14
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = True
.PivotItems("15").Visible = False

Case Is = 15
.PivotItems("10").Visible = False
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = True

End Select

End With

End If

If Singleweek_or_Accumulated = 2 Then
' accumulated weeks scenario

With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = True
End With

With ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
Select Case Week_number

Case Is = 10
.PivotItems("10").Visible = True
.PivotItems("11").Visible = False
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 11
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = False
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 12
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = False
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 13
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = False
.PivotItems("15").Visible = False

Case Is = 14
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = False

Case Is = 15
.PivotItems("10").Visible = True
.PivotItems("11").Visible = True
.PivotItems("12").Visible = True
.PivotItems("13").Visible = True
.PivotItems("14").Visible = True
.PivotItems("15").Visible = True

End Select

End With
End If


End Sub

Bob Phillips
04-04-2007, 02:08 AM
Sub Makro1()
Dim Singleweek_or_Accumulated As Integer
Dim Week_number As Integer
Dim PT

Application.ScreenUpdating = False

Singleweek_or_Accumulated = Range("J27")
' if J27 is = 1 the I will show singel week in the table
' if J27 is = 2 the I will show accumulated weeks in the table

Week_number = Range("J29")
' J29 is the cell where i select the week number

Set PT = ActiveSheet.PivotTables("Pivottabell5").PivotFields("Week")
If Singleweek_or_Accumulated = 1 Then
' singel week scenario
Call SetupPivot(PT, True, True, True, True, True, True)


Select Case Week_number

Case Is = 10
Call SetupPivot(PT, True, False, False, False, False, False)

Case Is = 11
Call SetupPivot(PT, False, True, False, False, False, False)

Case Is = 12
Call SetupPivot(PT, False, False, True, False, False, False)

Case Is = 13
Call SetupPivot(PT, False, False, False, True, False, False)

Case Is = 14
Call SetupPivot(PT, False, False, False, False, True, False)

Case Is = 15
Call SetupPivot(PT, False, False, False, False, False, True)

End Select

End If

If Singleweek_or_Accumulated = 2 Then
' accumulated weeks scenario

Call SetupPivot(PT, True, True, True, True, True, True)

Select Case Week_number

Case Is = 10
Call SetupPivot(PT, True, False, False, False, False, False)

Case Is = 11
Call SetupPivot(PT, True, True, False, False, False, False)

Case Is = 12
Call SetupPivot(PT, True, True, True, False, False, False)

Case Is = 13
Call SetupPivot(PT, True, True, True, True, False, False)

Case Is = 14
Call SetupPivot(PT, True, True, True, True, True, False)

Case Is = 15
Call SetupPivot(PT, True, True, True, True, True, True)

End Select

End If

End Sub

Private Sub SetupPivot(ByRef PT, ParamArray VisibleFlags())
Dim i As Long
With PT
For i = 10 To 15
.PivotItems(CStr(i)).Visible = VisibleFlags(i - 10)
Next i
End With
End Sub

Norie
04-04-2007, 05:56 AM
Cross post (http://www.mrexcel.com/board2/viewtopic.php?t=266021).