PDA

View Full Version : VBA Code running in f8 but not f5



Deen444
01-27-2018, 11:22 AM
Hello,

I am having some trouble with my code. It is running in f8 but not in f5. When I run it in f5 item is not recognized (showing item = Nothing). I have pasted the code below.


Sub SlicerSelect()

'On Error Resume Next

Worksheets("Sheet3").Activate

'Dim cache As Excel.SlicerCache
Dim item As SlicerItem
Dim Eval_Cell As String


Offset_Cell = Sheets("Sheet3").Range("A18").Address

A = 1: AA = 0: AAA = 0
For A = 1 To 2
AA = AA + 1
If AA = 1 Then
Slicer_Name = "Slicer_Color"
ElseIf AA = 2 Then
Slicer_Name = "Slicer_Letter"

End If

'Set cache = ActiveWorkbook.SlicerCaches(Slicer_Name)
BB = 0
Do Until End_Switch = 1
AAA = AAA + 1: BB = BB + 1: Eval_Cell = Range(Offset_Cell).Offset(0, CStr(AAA))
If BB = 1 Then
'''
Else
For Each item In ThisWorkbook.SlicerCaches(Slicer_Name).SlicerItems
If item.Name = Eval_Cell Then
item.Selected = True
Else
item.Selected = False
End If
Next item
End If
If IsEmpty(Eval_Cell) Then End_Switch = 1
Stop
Loop
End_Switch = 0

Next A

End Sub

SamT
01-27-2018, 12:01 PM
The first thing I would try is lose all the "Next Line" Colons, and use actual line feeds.

offthelip
01-27-2018, 04:14 PM
I have had problems before with routines that work in single step mode but not when run normally, this can be due to timing problems within EXCEL specially if it is multithreading.
the way I solved it is to force a recalculation of the worksheets using the following code just before the "unrecognised" bit:


Worksheets("Sheet1").EnableCalculation = False
Worksheets("Sheet1").EnableCalculation = True