Formula Versatility Required

[nigelk]

HI all,

I'm using Excel 2003 and vba

I'm putting sumproduct formulas into cells with:

.Formula=SUMPRODUCT(($S$2:$S$10=$AF$2)...........

I need to change the $S$10 part into something more versatile. I'm using LastRow as a variable and need to re-write it along the lines of:

.Formula=SUMPRODUCT(($S$2 & ":" & LastRow=$AF$2)

Can anyone help?
Thanks,Nigel

 

[JackDanIce]

Try:

.Formula= "=SUMPRODUCT($S$2:S" & LastRow & "=$AF$2)"

 

[Colin Legg]

Hi Nigel,

Like this:

.Formula= "=SUMPRODUCT(($S$2:$S$" & LastRow & "=$AF$2)......."

 

[Andrew Poulsom]

It would be, eg:

.Formula = "=SUMPRODUCT(($S$2:$S$" & LastRow & "=$AF$2)*($A$2:$A$" & LastRow & "))"

 

[nigelk]

Thank you all for your replies. Problem now solved.

Nigel