Select Case and different criteria

[rob nobel]

Can someone please help me to modify this code (with thanks to Dave
Peterson!)?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pvttable As PivotTable
If Intersect(Target, Me.Range("L26:L1525")) Is Nothing Then Exit Sub
Select Case UCase(Me.Cells(Target.Row, "F").Value)
Case Is = UCase(Me.Range("F16").Value)
Set pvttable = Sheet15.PivotTables("PivotTable1")
End Select
If pvttable Is Nothing Then Exit Sub
End Sub

I need ....
Set pvttable = Sheet15.PivotTables("PivotTable1") to depend on the following
2 criteria.
1.Case Is = UCase(Me.Range("F16").Value) ....(which is already there)
2. When the cell in the same row in column H is >0

If both those conditions are not true, I need it to ....
Set pvttable = Sheet16.PivotTables("PivotTable1") to depend on the
following 2 criteria.
1.Case Is = UCase(Me.Range("F16").Value) ....(which is already there)
2.When the cell in the same row in column I is >0

(Please note the different sheet and column names.)

Rob

 

[Trevor Shuttleworth]

Rob

untested but ...

Select Case UCase(Me.Cells(Target.Row, "F").Value)
Case Is = UCase(Me.Range("F16").Value)
If Me.Cells(Target.Row, "H").Value > 0 Then
Set pvttable = Sheet15.PivotTables("PivotTable1")
End If
End Select

Regards

Trevor

 

[Bob Phillips]

Rob,

Again untested, but try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim pvttable As PivotTable
If Intersect(Target, Me.Range("L26:L1525")) Is Nothing Then Exit Sub
Select Case UCase(Me.Cells(Target.Row, "F").Value)
Case Is = UCase(Me.Range("F16").Value)
If Cells(Target.Row, "H") > 0 Then
Set pvttable = Sheet15.PivotTables("PivotTable1")
ElseIf Cells(Target.Row, "I") > 0 Then
Set pvttable = Sheet16.PivotTables("PivotTable1")
End If
End Select
If pvttable Is Nothing Then Exit Sub
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[rob nobel]

Hi Bob,
Thank you very much for your assistance. Initual testing seems to do fine.
Rob

 

[rob nobel]

Thanks Trevor, I had a look at your suggestion and it seemed the condition
of row I was not checked in the code. I therefore used Bob's suggestion but
looking at yours again I realise it was just a matter of me adding that bit
to what you had suggested. (Sorry....I must be lazy)

Rob