No announcement yet.

Excel VBA Compile error: Expected: List separator or )

  • Filter
  • Time
  • Show
Clear All
new posts

  • Excel VBA Compile error: Expected: List separator or )

    Dear All,

    My overall code is to, on click into a cell in a calendar made in excel, output some data into a cell named LiveOutput. To do this I am trying to convert a conditional formatting rule into a piece of VBA in order to formulate the elseif statement. The code (which is incomplete for the complete task) can be seen below.
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim LiveCalendar As Range
    Dim LiveYear As String
    Dim LiveBusArea As String
    Dim LiveOutput As String
    Dim LiveStart As Range
    Dim LiveEnd As Range
    Dim LiveLength As Range
    LiveYear = ThisWorkbook.Names("LiveYear").RefersTo
    LiveYear = Mid(LiveYear, 3, Len(LiveYear) - 3)
    LiveBusArea = ThisWorkbook.Names("LiveBusArea").RefersTo
    LiveBusArea = Mid(LiveBusArea, 3, Len(LiveBusArea) - 3)
    LiveOutput = ThisWorkbook.Names("LiveOutput").RefersTo
    LiveOutput = Mid(LiveOutput, 3, Len(LiveOutput) - 3)
    Set LiveCalendar = ThisWorkbook.Names("LiveCalendar").RefersToRange
    Set LiveStart = ThisWorkbook.Names("LiveStart").RefersToRange
    Set LiveEnd = ThisWorkbook.Names("LiveEnd").RefersToRange
    Set LiveLength = ThisWorkbook.Names("LiveLength").RefersToRange
        With ActiveWindow
            If ActiveCell.Value > 0 And IsNumeric(ActiveCell.Value) = True And InRange(ActiveCell, LiveCalendar) Then
            ' Yes, We are in the calendar and it is a number over 1
                Range("LiveOutput").Value = ActiveCell.Value
            ElseIf Application.IsText(ActiveCell.Value) = True And InRange(ActiveCell, LiveCalendar) And (Application.WorksheetFunction.CountIfs(Indirect(LiveBusArea, ""=Y"" ,LiveStart,DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2))) + Application.WorksheetFunction.CountIfs(INDIRECT(LiveBusArea),""=Y"",LiveEnd,DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2)),LiveLength,"">1"") + COUNTIFS(INDIRECT(LiveBusArea,""=Y"",LiveEnd,"">"" & DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2)),LiveStart,""<"" & DATE(LiveYear,R(Row(Activecell)-4/2)C2,R4C((Column(ActiveCell)-1)/2))))>0" Then
            'Within range but day of the week
                Range("LiveOutput").Value = ActiveCell.Value
            'Blank so make the output box blank
                Range("LiveOutput").Value = ""
            End If
        End With
    End Sub
    Function InRange(Range1 As Range, Range2 As Range) As Boolean
    ' returns True if Range1 is within Range2
    Dim InterSectRange As Range
        Set InterSectRange = Application.Intersect(Range1, Range2)
        InRange = Not InterSectRange Is Nothing
        Set InterSectRange = Nothing
    End Function
    When trying to compile the elseif line, I am receiving the error message "Compile error: Expected: List separator or )" at the first ""=Y"", specifically highlighting the 2nd "". This line has been modified from writing a macro with the macro recorder to capture the formula. This part has not been adapted from this so I am at a bit of a loss as to why it is not working and how I can get past this point.

    Thank you for any help offered.


  • #2
    Re: Excel VBA Compile error: Expected: List separator or )

    My head hurts just looking at it...

    You use "Indirect", but this is not a VBA method/property/function... it should application.indirect, if you mean to use the Excel function.

    If you ARE using Indirect, then the syntax is wrong, because indirect only takes two arguments... the first one being a reference to a cell and the second argument being the type of reference... so this piece is definitely incorrect VBA syntax and two sides... (the incorrect use of the function name AND the incorrect parameters for the function)....

    Application.WorksheetFunction.CountIfs(Indirect(LiveBusArea, ""=Y"" ,
    Maybe.... this?

    Application.WorksheetFunction.CountIfs(application.Indirect(LiveBusArea), ""=Y"" ,

    Thats just a guess though. Aside from that, I would strongly urge you in the strongest possible terms, to simplify whatever it is you are trying to do.


    In addition

    Check out our new reputation system. Click on the "star" under the post!

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend



    • #3

      Re: Excel VBA Compile error: Expected: List separator or )

      Thank you Ger. This has been a head hurter for me Today, and the worrying thing is this is likely the simpler bit! (The full code would copy items from a table based on certain rules). I was indeed trying to use the Excel function rather than the VBA function. I will have a look again tomorrow and either hopefully it will work with some "application." added or it will be ditched for something simpler.

      Thank you again.