SUMPRODUCT question

[grunschlange]

I have a file that tracks the number of case reviews coming due for the next 3 months. I want to use a SUMPRODUCT formula in a module to count if a client number in column N on a sheet called "ABC Cases-May" matches with an F-type case on a master sheet called "May":

ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",IF(SUMPRODUCT(--(May!R3C14:R10000C14=RC[-1]),--(May!R3C7:R10000C7=""F""))=1,""Yes"",""""))"

The formula works fine when the month of reviews is "May", but the months change, and the sheet names change with them. How would I go about making a sheet reference in the formula to a constantly changing sheet name?

Thanks.

 

[Peter_SSs]

Assuming the month name to be inserted in the formula is whatever the current month is, then here is one way

Dim frmla As String

Const FormulaBase As String = _
    "=IF(RC[-1]="""","""",IF(SUMPRODUCT(--(#!R3C14:R10000C14=RC[-1]),--(#!R3C7:R10000C7=""F""))=1,""Yes"",""""))"

frmla = Replace(FormulaBase, "#", Format(Date, "mmmm"))
ActiveCell.FormulaR1C1 = frmla

Note, however, that you generally do not need to 'select' a cell (or range etc) to work with it in vba and selecting slows your code considerably. So if you were, say, going to enter this in column B from cell B2 down to wherever the data ends in column A, you could do it all at once, without selecting anything by replacing the last line of the code above with this one

Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).FormulaR1C1 = frmla

If you need more help with working without selecting, tell us more about your current code, what you have in your sheet, where and what you are trying to achieve.