Use CountIFS in VBA with variable criteria

[PROACT]

Hi I've been trying this for a decade (it seems like that) and searched the whole web, but probably it is just some dots, slashes and other punctual stuff thats wrong with my code.

Ok what I'm trying to do is rather easy, I would like to put a countifs formula in a cell by VBA.
The more difficult part is, the source data is in another workbook, and the criteria must variable.

So in the example the variables are:

• Project
• Year
• Month

I've wel declared them as integers, the value of the variables are linked. But I can't get that COUNTIFS-formula working.

Can anyone please help me out?
Thanks in advance...

ActiveCell.Formula = _            "=COUNTIFS('[Masterfile contacten_RAW_Data.xlsx]Data'!R2C11:R99999C11,ProjectId,'[Masterfile contacten_RAW_Data.xlsx]Data'!R2C18:R99999C18,Year,'[Masterfile contacten_RAW_Data.xlsx]Data'!R2C19:R99999C19,Month"

 

[steve the fish]

Try like this:

ActiveCell.Formula = _
    "=COUNTIFS('[Masterfile contacten_RAW_Data.xlsx]Data'!R2C11:R99999C11," & ProjectId & ",'[Masterfile contacten_RAW_Data.xlsx]Data'!R2C18:R99999C18," & myYear & ",'[Masterfile contacten_RAW_Data.xlsx]Data'!R2C19:R99999C19," & myMonth & ")"

 

[steve the fish]

Oh I changed month and year to myMonth and myYear because they could be confused with genuine excel functions.

 

[Domenic]

Try...

ActiveCell.FormulaR1C1 = "=COUNTIFS('[Masterfile contacten_RAW_Data.xlsx]Data'!R2C11:R99999C11," & ProjectId & ",'[Masterfile contacten_RAW_Data.xlsx]Data'!R2C18:R99999C18," & Year & ",'[Masterfile contacten_RAW_Data.xlsx]Data'!R2C19:R99999C19," & Month & ")"

Also, since COUNTIFS is not an array formula, using whole column references shouldn't adversely affect the efficiency of the formula...

ActiveCell.FormulaR1C1 = "=COUNTIFS('[Masterfile contacten_RAW_Data.xlsx]Data'!C11:C11," & ProjectId & ",'[Masterfile contacten_RAW_Data.xlsx]Data'!C18:C18," & Year & ",'[Masterfile contacten_RAW_Data.xlsx]Data'!C19:C19," & Month & ")"

Hope this helps!

 

[PROACT]

thanks a lot guys!!! you've saved me some time....