CODEWITHSUNDEEP
vbaexcel
Joel Biffin
Joel Biffin

Reputation: 378

Argument not Optional - Simple VBA function

I keep getting the Argument not optional compiler error in vba and can't figure out why.

I can't see any typos, I don't even take any arguments and the subroutine only executes on a button click.

Public Sub SearchAMLinesForIDs_Click()
    Dim time As String
    time = "AM"

    Dim end_row As Long
    end_row = Range("A2").End(xlDown).row


    Dim lookup_end_row As Long
    Dim lookup_result As Long

    lookup_end_row = ThisWorkbook.Sheets(time & "_Lines").Range("A2").End(xlDown).row

    lookup_table = ThisWorkbook.Sheets(time & "_Lines").Range("A2:C" & lookup_end_row)

    Dim row As Long

    For row = 1 To end_row
        lookup_result = WorksheetFunction.VLookup(Range("A" & row), lookup_table, 1, False)

        Cells(row + 1, 2) = lookup_result
    Next row

End Sub

Upvotes: 0

Views: 1969

Answers (3)

Tim Williams
Tim Williams

Reputation: 166126

Public Sub SearchAMLinesForIDs_Click()

    Dim sTime As String, shtData As Worksheet, sht As Worksheet
    Dim lookup_result, rngLookup As Range
    Dim rw As Long

    sTime = "AM"
    Set shtData = ThisWorkbook.Worksheets(sTime & "_Lines")
    Set sht = ActiveSheet

    Set rngLookup = shtData.Range(shtData.Range("A2"), shtData.Cells(shtData.Rows.Count, 1).End(xlUp))

    For rw = 2 To sht.Range("A2").End(xlDown).row

        'don't use Worksheetfunction, but test the return value for errors...
        lookup_result = Application.VLookup(sht.Cells(rw, 1), rngLookup, 1, False)

        sht.Cells(rw, 2) = IIf(IsError(lookup_result), "NoMatch!", lookup_result)

    Next rw

End Sub

Upvotes: 1

addohm
addohm

Reputation: 2455

Utilize your object browser if you're running into issues like this.

enter image description here

You can test your vlookup within a cell in a worksheet to verify validity.

Upvotes: 1

nagarajannd
nagarajannd

Reputation: 715

Your parenthesis are in incorrect order.

lookup_result = WorksheetFunction.VLookup(Cells(1 + Row, 1), _
                                          ThisWorkbook.Sheets(Time & "_Lines").Range(Cells(2, 1), Cells(lookup_end_row, 1)), _
                                          1, _
                                          False)

parenthesis opened for Range doesn't close and closed in the end.

Upvotes: 0

Related Questions