counter question

[Mark J]

I have the following code;

Dim Flds As Variant
Dim N As Integer
Public Sub Process2()
Range("B5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
N = 1
If Cells(5, 2) > 2.5 Then
Cells(5, 7) = N + 1
End If

what i do is refresh a pivot table each minute, but i need cells(5,7) to
count up 1 increment for each time the value in cells(5,2) is > than 2.5.

any way to adjust this so it works?.thanks

 

[JLGWhiz]

Dim Flds As Variant
Dim N As Integer
Public Sub Process2()
Range("B5").Select
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
If Cells(5, 2) > 2.5 Then
N = N + 1
Cells(5, 7) = N
End If

 

[Mark J]

I ran this a few times, but it is not incrementing up each time the pivot
table is refreshed, any ideas?

 

[Bob Phillips]

Try

Cells(5, 7) = Cells(5, 7) + 1


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JLGWhiz]

It only increments if the 2.5 value is exceeded, so maybe it is not exceeding
that value between refresh intervals.

 

[Mark J]

that works, thank you both for your talent and time.

 

[JLGWhiz]

Mark, the way the macro is written, I will check Cells(5, 2) immediately upon
the Pivot Table refresh. If at that instant the cell is > 2.5 It will
increment by by one. Since I don't know what Cells(5, 2) is measuring, I
don't know if it would ever be at 2.5 immediately after a refresh. Maybe
movint the If statement in front of the refresh statement could make a
difference by checking it before the refresh rather than immediately after.