CODEWITHSUNDEEP
vbaexcelfunction
Aspiring Developer
Aspiring Developer

Reputation: 680

Using a Function within a Sub Procedure VBA

I don't think I'm searching the right terms as I imagine this answer has to be somewhere but I am new to functions and trying to figure out how to get this to work:

How can I get my function findLastRow to be used where I say Range("B2" & findLastRow).Select ? The current error I recieve is Argument Not Optional and it highlights findLastRow

Public Function findLastRow(col As String) As Long 'making the macro available to all Sub Procedures

        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(1048576, col).End(xlUp).Row 'finding the last possible row disregarding blanks
        End With

        findLastRow = lastRow
End Function

Sub Open_INV()

    ChDir "C:\Users\MonthEnd_Close\Import_Files"
    Workbooks.OpenText Filename:= _
        "C:\Users\MonthEnd_Close\Import_Files\INV", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

    On Error Resume Next

    Range("B2").Formula = "=TRIM(A2)"
    Range("B2").Copy
    Range("B2:B" & findLastRow).Select
    Selection.PasteSpecial xlPasteFormulas
End Sub

Upvotes: 1

Views: 1416

Answers (1)

user4039065
user4039065

Reputation:

Don't use ActiveSheet in a function. You have a perfect opportunity to pass in a range that has a defined parent worksheet rather than a string representing the column letter.

Public Function findLastRow(col As range) As Long 'making the macro available to all Sub Procedures

        Dim lastRow As Long
        With col.parent
            lastRow = .Cells(.rows.count, col.column).End(xlUp).Row 'finding the last possible row disregarding blanks
        End With

        findLastRow = lastRow

End Function

Sub Open_INV()
    Workbooks.OpenText Filename:= _
        "C:\Users\MonthEnd_Close\Import_Files\INV", _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

    with activeworkbook.worksheets(1)
        .Range("B2:B" & findLastRow(.range("A2"))).Formula = "=TRIM(A2)"
    end with
End Sub

Upvotes: 1

Related Questions