Month function throwing type mismatch in VBA

[kyddrivers]

I have a file with 500+ rows of data. I would like to write a script that will loop thru and do 1 calculation (ABC) if month(C2)-Month(D2) = 0 or do a different calculation (XYZ) if month(C2)-Month(D2) <> 0

Columns C & D are formatted as Date

ID	Week	Week Start	Week End
3	1	1/1/2023	1/7/2023	ABC
3	5	1/29/2023	2/4/2023	XYZ

Sub Calc_Test ()
Dim Calc as Integer

Calc = Application.Evaluate("Month(R2C3)-Month(R2C4)")
' rest of the script goes here
End Sub

Each time I run the code, I get a Type 13 Mismatch at the calc variable. I have tried setting Calc as Long, getting the same error.

How do I assign the result of month(C2)-Month(D2) to the Calc variable so I can proceed from there?

Thanks!

 

[Fluff]

Do you use R1C1 notation in your sheets, or A1 notation?

 

[kyddrivers]

Do you use R1C1 notation in your sheets, or A1 notation?

If I am working directly on a worksheet, I use A1; whenever I write a script, I always use R1C1. If you are asking if the R1C1 reference style is checked in formula options, the answer is no.

 

[Fluff]

I use A1

In that case the evaluate needs to use A1 notation as well.

 

[kyddrivers]

In that case the evaluate needs to use A1 notation as well.

Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kyddrivers]

How do I increment or offset the evaluate function?

Calc = Application.Evaluate ("Month(D2)-Month(C2)")

I tried writing this, but the return is always 0

Dim Calc As Integer
Dim WeekStart As Range
Dim WeekEnd As Range

Set WeekStart = Range("C2")
Set WeekEnd = Range("D2")

Calc = Application.Evaluate("Month(" & WeekEnd & ")" & "-Month(" & WeekStart & ")")

Set WeekStart = WeekStart.Offset(1, 0)
Set WeekEnd = WeekEnd.Offset(1, 0)

Thanks!

 

[Joe4]

If you are going to use evaluate, I think it is looking for the physical address of the cell.

So maybe try:

Calc = Application.Evaluate("Month(" & WeekEnd.Address & ")" & "-Month(" & WeekStart.Address & ")")

However, all of that really seems unnecessary. VBA has a MONTH function too.
So if you are using range variables, you could just use this much simpler VBA formula instead:

Calc = Month(WeekEnd) - Month(WeekStart)

 

[kyddrivers]

I don't know why I try to over complicate things. KISS.

Thanks!!