CODEWITHSUNDEEP
excelvba
ilarson007
ilarson007

Reputation: 157

Getting a "Compile Error: Expected: End of Statement" when trying to return a calculated value from a function

I have coded a function that finds the first column that has an empty cell in the third row, but I cannot figure out how to return that integer.

Function FindEmptyColumn()
' Find the first empty column to paste data into

    ThisWorkbook.Activate
    Worksheets("Black Cam Raw Data").Activate
        Dim dCounter As Double
    dCounter = 1
    Dim iColumn As Integer
    Do Until Cells(3, dCounter) = ""
        iColumn = dCounter
        dCounter = dCounter + 1
    Loop
    iColumn = iColumn + 1
Return iColumn

End Function

Sub CMM_93Cam(ByVal wbTempName As Workbook)
'
' CMM_93Cam Macro
' This subroutine imports data from the CMM Report for a 195C93 Cam flatness measurement into this file.
'
    iColumn = FindEmptyColumn()

Basically, I get an error when I type the "Return iColumn" statement. How do I get the function to return the value I have stored in iColumn?

Upvotes: 2

Views: 11560

Answers (1)

mwolfe02
mwolfe02

Reputation: 24227

The Return statement in VBA does not return the results of a function. Use the function name instead.

Instead of: 

Return iColumn

Use:

FindEmptyColumn = iColumn

In VBA, the Return statement is only used in conjunction with GoSub.

MSDN: GoSub...Return Statement

Upvotes: 9

Related Questions