Select Case and different criteria

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

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
 
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
 
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)
 
Hi Bob,
Thank you very much for your assistance. Initual testing seems to do fine.
Rob
 
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
 
Back
Top