CODEWITHSUNDEEP
excelvba
user43792
user43792

Reputation: 13

Error in Excel VBA function: "Object required"

I am getting an error while writing a function for excel. The purpose of the function is to look down the entire date column of a table and, for every match in that column, subtract the cost value (in the adjacent column) of that row from a total value. I have written a function in VBA, but I can't even start debugging it because I am stuck at the first error. The error says "Object required" and highlights the first line, but it seems like I am passing objects into the function already (Ranges). Here is my code. 

Public Function WeeklyAllowance(firstCell As Range, lastCell As Range)
    Dim funds As Double
    Dim today As Date
    Dim thisWeekday As Integer
    Dim earlierDay As Date

    Set funds = 20
    today = Now()
    thisWeekday = Weekday(today)
    earlierDay = today - thisWeekday + 1 'This should be sunday at first

    Do Until earlierDay = today + 1 'Basically has one iteration for each day of the week until today
        Dim cell, column As Range
        Set column = Range(firstCell, lastCell)

        For Each cell In column     'Iterate through the cells in the column and check the dates
            If (cell.Value = earlierDay) Then
                Dim cellCol, cellRow As Integer
                cellCol = cell.column
                cellRow = cell.Row

                funds = funds - Sheet1.Cells(cellRow, cellCol + 1)
            End If

        Next cell

        earlierDay = earlierDay + 1 'Increment the day to check
    Loop

    WeeklyAllowance = funds
End Function

Could anyone help me with this please?

Upvotes: 1

Views: 1502

Answers (2)

user1644564
user1644564

Reputation: 385

Dim cell, column As Range

Wrong.

Dim cell As Range, column As Range

If your looping through days of the week then instead of adding 1 then use the dateadd function and parameter for weekdays.

Upvotes: 0

Rusan Kax
Rusan Kax

Reputation: 1894

Set funds = 20 is the syntax used for objects. But you have above defined funds as a double (which is not an object type in VBA). Just remove the Set keyword:

funds=20

Upvotes: 1

Related Questions