VBA Exit Subroutine Command Within A Function

Question

I have a function whose purpose is to take in a string and search the first row of a spreadsheet for cells that contain that string, and if they do it selects that column and assigns its value to a variable. However, if none contain that string I would like it to exit the subroutine completely. I've tried throwing the Exit Sub command within the function, but I keep getting error messages. The function is never called directly by the main sub, but from other functions that are called by the sub. Does anybody have any ideas on how I can get this to work? Here's the code that I have for the function:

Function ColSearch(Heading As String) As Integer
'Determines the column number of the desired heading

Sheets("CS-CRM Raw Data").Select
Sheets("CS-CRM Raw Data").Unprotect

    On Error Resume Next
    If Sheets("CS-CRM Raw Data").Cells.Find(What:=Heading, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Column > 0 Then
        myCol = Sheets("CS-CRM Raw Data").Cells.Find(What:=Heading, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Column
    Else
        End
    End If

ColSearch = myCol

End Function

Just a hint - when you write On Error Resume Next you should also ensure to write On Error Goto 0 or On Error Goto <MyErrorLabel> somewhere downstream. — paulroho
– paulroho, Commented Jul 21, 2014 at 14:48

Bmo · Accepted Answer · 2014-07-21 14:45:25Z

1

You just need to add an Exit Function to return.

answered Jul 21, 2014 at 14:45

Bmo

1,22211 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Exit Subroutine Command Within A Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related