CODEWITHSUNDEEP
vbaexcel
TonyP
TonyP

Reputation: 333

Argument not Optional Error

I am building a sheet to extract data from a list of job openings that I need to sort and filter by location and by BU. I need the code to count the number of openings then pass that information back to the main sub to use in creating additional pages and to loop the sub. I keep getting the above error in this segment. Any thoughts on what I am doing wrong?

Sub Organize_Data()

    Dim A As Integer
    Dim B As Integer
    Dim C As Integer

    Worksheets.Add().Name = "Calculations"
    Find_Unit
    Find_Locations
    Count_BU_Data
    Count_Country_Data
    Count_Raw_Data
End Sub

Function Count_BU_Data(A As Integer)

   ActiveWorkbook.Worksheets("Calculations").Range("B3", Worksheets("Calculations").Range("B3").End(xlDown)).Rows.Count
End Function

Upvotes: 2

Views: 516

Answers (1)

SierraOscar
SierraOscar

Reputation: 17637

Your UDF:

Function Count_BU_Data(A As Integer)

Takes an argument (A As Integer) which you haven't specified as being an Optional argument. You call the function from your other routine without supplying this argument:

Sub Organize_Data()

    Dim A As Integer
    Dim B As Integer
    Dim C As Integer

    Worksheets.Add().Name = "Calculations"
    Find_Unit
    Find_Locations
    Count_BU_Data    '// <~~ No argument passed to function.
    Count_Country_Data
    Count_Raw_Data
End Sub

Hence the 'Argument not optional' error.

Seeing as that function doesn't appear to actually use the argument, you can either remove it from the function header:

Function Count_BU_Data() As Long '// Note I've included a return value...

Or make it an optional argument

Function Count_BU_Data(Optional A As Integer) As Long

You can also specify a default value if the optional argument isn't supplied

Function Count_BU_Data(Optional A As Integer = 1) As Long

Upvotes: 3

Related Questions