CODEWITHSUNDEEP
vbaexcel
user2952558
user2952558

Reputation: 3

VBA Type Mismatch on Year Function as CountIfs criteria

I'm getting a type mismatch error from the following code:

blattZFq3.Cells(month, siaw) = Application.WorksheetFunction.CountIfs(Worksheets(i).Range("AF10:AF290"), month, Year(Worksheets(i).Range("AE10:AE290")), minYear)

I'm guessing it's a problem with the second criteria, more specifically the Year function as criteria for a range since the code worked fine in a previous version with just the first criteria and using countif.

minYear is declared as Variant and has been assigned the value of 2012 by a previous function.

Basically I want the cell in the range blattZFq3 to contain the number of times a number matching month occurs in a column, but only if the year of a date in the same row but different column matches minYear.

Does anybody have any suggestions?

Thanks in advance....

Upvotes: 0

Views: 990

Answers (2)

SeanC
SeanC

Reputation: 15923

You can't do this function to the array: Year(Worksheets(i).Range("AE10:AE290")) as it's expecting a range for the second area to check.
Also, I would avoid using the word Month as a variable name, as it's also the name of a function.

You will have to write the function with 3 criteria to get around the restriction, or write a formula into the target area.

Function with 3 criteria:

blattZFq3.Cells(MyMonth, siaw) = _
    WorksheetFunction.CountIfs(Worksheets(i).Range("AF10:AF290"), MyMonth, _
    Worksheets(i).Range("AE10:AE290"), ">=" & DateSerial(minYear, 1, 1), _
    Worksheets(i).Range("AE10:AE290"), "<=" & DateSerial(minYear, 12, 31))

As a formula into the cell:

blattZFq3.Cells(MyMonth, siaw).Formula = _
    "=SUMPRODUCT(--(SheetName!AF10:AF290=" & MyMonth & ")," & _
    "--(YEAR(SheetName!AE10:AE290)=" & minYear & "))"

Upvotes: 1

Makah
Makah

Reputation: 4523

Work with date sometimes is tricky. Are you using english version? You can try to write the same formula code in Excel and test it before you put it VBA. You can also try something like:

blattZFq3.Cells(month, siaw) = "=CONTIFS(.....)"

Upvotes: 0

Related Questions