Results 1 to 3 of 3

compile error expected: (

Threaded View

  1. #1
    Forum Contributor
    Join Date
    03-10-2017
    Location
    USA
    MS-Off Ver
    office 2016
    Posts
    393

    compile error expected: (

    I was all done with this code and it worked fine then all of a sudden i start getting this error... cant see why. I even cut all of the code out leaving just the private sub line() and end sub and i still get error. im very confused

    Private Sub line()
    
    Dim r As Range
        Dim shp As Shape
        Dim shp1 As Shape
        Dim shp2 As Shape
        Dim H As Double 'high
        Dim l As Double 'low
        Dim j As Double 'check number
        Dim clr As Long 'RGB
        Dim x1 As Integer
        Dim x2 As Integer
        Dim y1 As Integer
        Dim y2 As Integer
        Dim LW As Integer
        
        
    
    For Each shp In ActiveSheet.Shapes
        If shp.Name Like "*Connector*" Then shp.Delete
    Next shp
    
        
        'define array and look at each row for max and min numbers
        For Each r In Range("d9:d179")
            H = Application.Max(Range(Cells(r.Row, 4), Cells(r.Row, 13))) 'add combobox for frequency
            l = Application.Min(Range(Cells(r.Row, 4), Cells(r.Row, 13)))
            
       'if largest number is min then assign color and line size dependent on variables in v, w and x
            Select Case l
                Case Is <= Sheet25.Cells(1, "v").Value
                clr = RGB(47, 117, 181) 'Dark blue
                LW = 4
                l = Abs(l)
                Case Is <= Sheet25.Cells(1, "w").Value
                clr = RGB(0, 161, 218) 'blue
                LW = 2.5
                l = Abs(l)
                Case Is <= Sheet25.Cells(1, "x").Value
                clr = RGB(189, 215, 238) 'Light blue
                LW = 1.5
                l = Abs(l)
                Case Else
                l = 0
            End Select
            
            'if largest number in row is max then assign color and line size dependent on variable in aa, z and y
            If H > l Then
                Select Case H
                    Case Is >= Sheet25.Cells(1, "aa").Value
                    clr = RGB(255, 0, 0) 'dark red
                    LW = 4
                    Case Is >= Sheet25.Cells(1, "z").Value
                    clr = RGB(255, 121, 121) 'red
                    LW = 2.5
                    Case Is >= Sheet25.Cells(1, "y").Value
                    clr = RGB(255, 213, 213) 'light red
                    LW = 1.5
                    Case Else
                    H = 0
                End Select
            End If
            
            j = Application.Max(H, l)
    
    'now find shape that coorelates to names in column 2 and 3 from r.row
       Set shp1 = ActiveSheet.Shapes(Cells(r.Row, 2).Text)
       Set shp2 = ActiveSheet.Shapes(Cells(r.Row, 3).Text)
       
    'find coordinates of each shape - center of each
       x1 = (shp1.Left + (shp1.Left + shp1.Width)) \ 2
       y1 = (shp1.Top + (shp1.Top + shp1.Height)) \ 2
       x2 = (shp2.Left + (shp2.Left + shp2.Width)) \ 2
       y2 = (shp2.Top + (shp2.Top + shp2.Height)) \ 2
       
       'draw line between shapes
            
            If j > 0 Then
                  Shapes.AddConnector(msoConnectorStraight, x1, y1, x2, y2).line.Weight = LW
                  '.Line.ForeColor.RGB = clr
                
            End If
            
            'reset RGB
            clr = 0
            j = 0
            
        Next
     
    End Sub
    Attached Files Attached Files
    Last edited by kevinu; 01-27-2018 at 12:04 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Compile Error: Expected End With
    By Sanjibghosh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-30-2017, 06:48 AM
  2. [SOLVED] Compile error: Expected end with???
    By OpOrange in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-22-2014, 08:27 AM
  3. Compile error: Expected End Sub
    By MacroCoder in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2013, 04:05 PM
  4. Compile Error: Expected End With?
    By Jeka__7 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2013, 10:30 AM
  5. HELP Getting a Compile Error: Expected End With
    By turtles62 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-27-2012, 11:27 PM
  6. Compile Error: Expected End Sub
    By Dcauth in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2011, 01:56 PM
  7. Compile Error: Expected End Sub
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-27-2011, 10:55 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1