Syntax problem with SumProduct and range?

[EssKayKay]

Hello,
I believe I am having a syntax problem with a formula. I can manually code it and all works fine. The problem is the range limits change (row number). I have a cell (T13) with a range name “LastPmtRow” that contains the last row number.

As example, the manually coded line that works is:
=SUMPRODUCT(--(YEAR($C$32:$C$143)=(R33)),$Q$32:$Q$143)

In this example, the location of the code in question is the row line 143. Cell T13 (range name "LastPmtRow") contains the value 143.
I unsuccessfully tried something like:
=SUMPRODUCT(--(YEAR($C$32:$C$ & range("T13").value)=(R33)),$Q$32:$Q$ range("T13").value)

Thanks for viewing,
Steve K.

 

[EssKayKay]

    '1.  COPY –
    Range("LookupFormula").Copy Range("P33")
  

    '2.  Change Format –
    With Range("F6:F8,F10:F12")
        .Locked = False
        .FormulaHidden = False
        With .Interior
            .Pattern = xlNone
            .TintAndShade = 0
        End With
    End With

    '3.  Clear –
    Range("F6:F8,F10,K6:K13,F15:F17,F22").ClearContents

Just a basic demo on the use of Select, run the 2 codes below on a blank sheet to see the result

Sub Without_Select()
    Dim i As Long, t As Double
    t = Timer

    For i = 1 To 1000
        Cells(i, "A").Value = i + 7
    Next

    MsgBox Timer - t
End Sub

Sub With_Select()
    Dim i As Long, t As Double
    t = Timer

    For i = 1 To 1000
        Cells(i, "B").Select
        Selection.Value = i + 7
    Next

    MsgBox Timer - t
End Sub

Edit: Indented the code

Yup Mark, I most certainly see what you are talking about.
Thanks for the tip/suggestion. I will try to modify my code.
Much appreciated. . .

 

[MARK858]

Change the ranges to suit, it assumes that R37 is a cell and it contains the year only.

If Range("R37").Value <= Year(Range("C93")) Then _
Range("S33").Formula = Replace("=SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)", "#", Range("T13").Value)

 

[EssKayKay]

Change the ranges to suit, it assumes that R37 is a cell and it contains the year only.

If Range("R37").Value <= Year(Range("C93")) Then _
Range("S33").Formula = Replace("=SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)", "#", Range("T13").Value)

Thanks Mark. I believe I may have mislead you with my request. I’m going to try to explain again. My problem is I don’t know the correct words for each action but I will try.

I’m looking for a formula to be placed in cell S33 that returns the value of the formula if the value is greater than zero. However, if the value is <= 0 then display nothing.

Except for displaying nothing if the value is zero, your original formula did most of what I wanted:

Range("S33").Formula = "=SUMPRODUCT(--(YEAR($C$32:$C$" & Range("T13").Value & ")=(R33)),$Q$32:$Q$" & Range("T13").Value & ")"

What I then do is copy the formula from S33 down a variable distant depending on how many rows are needed. This can vary from S40 to S2033. The cell S33 then changes to S34, S35, S36, etc.

This is where I was hoping the IF statement If(Formula>0,Formula,””) where the formula is:
SUMPRODUCT(--(YEAR($C$32:$C$" & Range("T13").Value & ")=(R33)),$Q$32:$Q$" & Range("T13").Value & ")

Or Peter’s formula:
SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)", "#", Range("T13").Value


Here is a snip-it of my existing code that is doing what I want but as I noted - very sloppy and in some cases maybe a bit ridiculous, but it works. Any suggestions here would be welcome.

Private Sub cmdTest_Click()
Application.ScreenUpdating = False

     Range("R33:U133").ClearContents
'. . . . . . . . . . . . . . . . . . .

     Range("R33") = "=Year(T22)"
'. . . . . . . . . . . . . . . . . . .

     Range("R34") = "=R33 + 1"
'. . . . . . . . . . . . . . . . . . .

     Range("R34").Select
     Selection.Copy
     Range("R34:R133").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
     Range("R32").Select
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

     Range("U33").Formula = "=SUMPRODUCT(--(YEAR($C$32:$C$" & Range("T13").Value & ")=(R33)),$Q$32:$Q$" & Range("T13").Value & ")"
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

     Range("U33").Select
     Selection.Copy
     Range("U33:U133").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

     Range("T24").Select
     Selection.Copy
     Range("S33").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
     ActiveCell.FormulaR1C1 = "=IF(RC[2]>0,RC[2],"""")"
'. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

     Range("S33").Select
     Selection.Copy
     Range("S33:S54").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False
     Range("S32").Select

End Sub

And again – thanks. . .

 

[Peter_SSs]

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)

 

[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)

Thank you very much Peter - that did it. This is much cleaner than my duplicating columns, copying, deleting, etc.

Again, much appreciated to all for your help. I hope you guys/gals realize how you are held in great esteem by us mere mortals. Without you our projects would never happen.

 

[Peter_SSs]

Thank you very much Peter - that did it.

You're welcome. Thanks for the confirmation.

.. and for your kind words.

 

[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.

OOPS - see new thread. . .