+ Reply to Thread
Results 1 to 4 of 4

Compile error when calling function in sub

Hybrid View

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Las Vegas
    MS-Off Ver
    Excel 2010
    Posts
    87

    Compile error when calling function in sub

    I have written the following Function to scroll through each row in a column searching for a particular value. If that value is found then the row is left alone. If the row contains anything else then delete the entire row.

    Function DeleteReferral2(TargetSheet As Worksheet, strFindMe As String)
    Dim k As Long
    
        With TargetSheet
            For k = .Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
                If .Cells(l, "A").Value = strFindMe Then
                Else
                .Cells(l, "A").EntireRow.Delete
            Next k
        End With
    
            
    End Function
    I have tried to call the Function in the following code. When I do, I get a "Compile error: Expected: =" as soon as I even type the code. I have highlighted the code in question in red.

    Private Sub CommandButton1_Click()
    Dim TargetSheet As Worksheet
    Dim strFind As String
    Dim Ctrl1 As Control
    Dim Ctrl2 As Control
    
        Call FillDateRange
    
        For Each Ctrl1 In RefTrack.Frame1.Controls
            If TypeName(Ctrl1) = "OptionButton" Then
                If Ctrl1.Value = True Then
                Set strFind = Ctrl1.Caption
                Else
                End If
             End If
        Next Ctrl1
        
        If Me.OptionButton10.Value = True Then
            Call CopySheets
                For Each Ctrl2 In RefTrack.Frame2.Controls
                    If TypeName(Ctrl2) = "CheckBox" Then
                        If Ctrl2.Value = True Then
                            If Ctrl2.Caption = "Information/Service Only" Then
                    Set TargetSheet = Sheets("Information Only")
                DeleteReferral2(TargetSheet, strFind)    
                
                    Else
    
                    End If
                Else
                End If
            End If
            
        Next Ctrl2
    Can anyone shed some light on what the issue is and how to possibly solve it?

    Thank you,

    Brian

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Compile error when calling function in sub

    Try removing the ( )

    DeleteReferral2 TargetSheet, strFind
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Las Vegas
    MS-Off Ver
    Excel 2010
    Posts
    87

    Re: Compile error when calling function in sub

    That worked. I have other functions where you do include the ( ) when passing variables to the function. I'm not sure I understand when the parentheses are required/optional and when they aren't

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Compile error when calling function in sub

    You need to include the ( ) when calling a function that returns a value/string/range or something to assign the returned value to a variable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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