Typicalusername
Typicalusername

Reputation: 33

How to extract a date and subtract it from today's date?

I have been trying to figure out how to extract a date and subtract it from today's date, then I need it to display yes or no based on it being outside or inside or 12 months range. I am just trying to figure out how to do the math without even making it into a fancy loop.

I added a lot of stuff because various forum posts tell other people they are missing option explicit or the variables aren't defined, but I'm still stuck getting just this to run.

The error message is

Compile error: Variable not defined.

I drew an arrow in my code where it claims to be <-----------

Private Sub CommandButton1_Click()
    calcDate
End Sub

Public Function calcDate() '<-------------- This is where the error is    
    Dim RegDate As Date
    Dim UpDate As Date

    Set RegDate = Sheets("2019").Cells(o, 17)
    Set UpDate = Sheets("2018").Cells(n, 2)

    answer = DateDiff("m", RegDate, UpDate)

    Sheets("Table").Cells(b, 2) = answer
End Function

Upvotes: 0

Views: 48

Answers (1)

teylyn
teylyn

Reputation: 35915

The Cells method has the syntax Cells(row,column). You are using a column first, and also that column needs to be in double quotes, otherwise it will be treated like a variable, and you have not declared these.

Cells(2,"n") will work.

Also, you haven't declared the variable answer.

Finally, you don't need Set when you assign a value to a date variable.

Option Explicit

Private Sub CommandButton1_Click()
    calcDate
End Sub

Public Function calcDate()

Dim RegDate As Date
Dim UpDate As Date
Dim answer As Integer

RegDate = Sheets("2019").Cells(17, "O")
UpDate = Sheets("2018").Cells(2, "N")

answer = DateDiff("m", RegDate, UpDate)

Sheets("Table").Cells(2, "B") = answer

End Function

Upvotes: 2

Related Questions