Consulting

Results 1 to 3 of 3

Thread: VBA Code running in f8 but not f5

  1. #1
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    1
    Location

    VBA Code running in f8 but not f5

    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
    Last edited by SamT; 01-27-2018 at 11:56 AM. Reason: Added Code Tags

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    The first thing I would try is lose all the "Next Line" Colons, and use actual line feeds.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •