Syntax problem with SumProduct and range?

[EssKayKay]

What about this for S33?

Range("S33").Formula = Replace("=IF(SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)>0,SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#),"""")", "#", Range("T13").Value)

Hello,

I’m back with an issue I’m having with a formula in a range of cells. This is in addition to my previous post (maybe I should have created a now post – not sure of the protocol). I’m not sure what is causing the problem. It appears the formula(s) are dependent on another set of cells. I’ll try to explain:

If I enter a value in K9, K11, or K13, it has an impact on formulas in column I (rows 33+) which affects results in column S (rows 33+). What’s weird is if I enter a small number (<11) in cells K9, 11, or 13 all works fine. However, if I enter a larger number (which varies between the K9,11,13 – some are >11, others >44), the values in column S display #Value!.

The value entered into cells K9, K11, K13 is displayed in the row in column I that corresponds to the date in cells K8, K10, or K12. That is, if the value in column I is greater than the accepted input, the #Value! is displayed in column S.

I have uploaded an abridged copy of my spreadsheet (TryMe) to DropBox.
Dropbox
I have removed most all coding leaving only one function in Module1. I did this in an effort to figure out what may be causing this problem.

As a display/example of what I am talking about, enter a large number (say 100) into either cell K9, K11, or K13 (yellow cells). You will see the #Value! appear in column S (green cells).

Thanks for viewing,
Steve K.

 

[EssKayKay]

Just a note on my previous post. I suspect there will be a problem trying to access my test file (TryMe.xlsm). Is there some other EASIER way to post/upload a spreadsheet?

EDIT - I believe the problem is based on something to do with the formula in column I33+. If I delete all the data in range I33:I256 the error does not appear - however I need all data in column I.

 

[Peter_SSs]

This is really a separate question so I'm moving it to a new thread.

When a small number (eg 11) is entered in K9 the dates in column C extend down to C254 which is referenced in the column S formulas.
When a large number (eg 200) is entered in K9 the dates in column C do not extend all the way down to C254 and the bottom cells in column C contain "" instead of a date. However the formulas in column S still reference cells down to row 254. The first part of those formulas return the year of the column C dates but of course "" does not have a year, hence the error.

 

[EssKayKay]

This is really a separate question so I'm moving it to a new thread.

When a small number (eg 11) is entered in K9 the dates in column C extend down to C254 which is referenced in the column S formulas.
When a large number (eg 200) is entered in K9 the dates in column C do not extend all the way down to C254 and the bottom cells in column C contain "" instead of a date. However the formulas in column S still reference cells down to row 254. The first part of those formulas return the year of the column C dates but of course "" does not have a year, hence the error.

Ahh thank you Peter, you found my problem. I think I have this working. I have to run the formula you created as noted in post #1 of this thread. After any entry is made in cells K9, K11, or K13, I run the following Sub:

Public Sub ResetPITI()
   Range("S33").Formula = Replace("=IF(SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)>0,SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#),"""")", "#", Range("T13").Value)
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
   Range("S33").Select
   Selection.Copy
   Range("S33:S" & Range("T13").Value).Select
   ActiveSheet.Paste
End Sub

Thank you once again for your help and patience with this old man. It truly is a pleasure hanging out here.

Again, much appreciated. . .
Steve K.

 

[Peter_SSs]

You're welcome. Glad it helped.