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.

 

[MARK858]

If this is in VBA then maybe

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

or

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

 

[EssKayKay]

Thank you Mark that appears to be doing what I want. I will play with this a bit more but I think I'm in the right direction.

Here's my (read your) code:

Private Sub cmdTest2_Click()
     Range("S33").Select     
     ActiveCell.Formula = "=SUMPRODUCT(--(YEAR($C$32:$C$" & Range("T13").Value & ")=(R33)),$Q$32:$Q$" & Range("T13").Value & ")"     
End Sub

Thanks again,
Steve

 

[MARK858]

I only used Activecell because you didn't state what cell it was going in so...

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

 

[EssKayKay]

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

I like that. A bit cleaner and works perfect.
Thanks one more time. . .

 

[MARK858]

You're welcome (avoid using .Select as 99% of the time code doesn't need it and it only slows the code down and causes screen flicker)

 

[EssKayKay]

You're welcome (avoid using .Select as 99% of the time code doesn't need it and it only slows the code down and causes screen flicker)

WOW, that is most interesting. I have used “.select” over 200 times in my project.

I have to note here, I am not a programmer and thus my coding is extremely sloppy – but for the most part, it works. I have been working on this on-going venture primarily as a learning tool for this retired guy (read me), plus it occupies my time.

I looked at my coding and it appears for the most part my “select” option happens numerous times under a few conditions (there are others).

Examples:

1.  COPY –
      Range("LookupFormula").Select
      Selection.Copy
      Range("P33:P" & Range("LastDataRow").Value).Select
      ActiveSheet.Paste

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

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

I assume these are situations you are talking about. Any suggestions?

Thanks again Mark for your insight – much appreciated,
Steve

 

[MARK858]

    '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

 

[Peter_SSs]

I like that. A bit cleaner and works perfect.

FWIW, I normally do it like this. The reason being that the actual formula part (blue below) looks much more like the actual formula that ends up in the worksheet & there is less struggling with how many and where to put all the quote marks. Also, if that last row number value is used in a lot of places in the formula, the vba formula string becomes very long. Using a place-holder like this and substituting the last row numbers all at once keeps that formula string shorter (as well as looking more like the actual worksheet formula).

Private Sub cmdTest2_Click()
     Range("S33").Formula = Replace("=SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)", "#", Range("T13").Value)
End Sub

 

[EssKayKay]

Thank you both for your input. This is going to be very helpful. I have one more question if you please. I hope I can explain this. . .

I want to display the results of your formulas in cell S33 (which will be copied later) only if the value is > zero. I tried this a couple different ways but this dummie is just not smart enough to get it to work. Here are a few examples of what I tried.

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

OR , maybe a bit simpler option could be something like the following where I test to see if the Year in column R is <= the Year in column C:

=IF(R37<=YEAR($C$93),style='font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-themecolor:
text1'> Range("S33").Formula = Replace("=SUMPRODUCT(--(YEAR($C$32:$C$#)=R33),$Q$32:$Q$#)", "#", Range("T13").Value),””)style='font-size:9.0pt;font-family:"Arial","sans-serif";color:black;mso-themecolor:
text1'>

But here is where I need $C$93 to read something like $C$ & Range(“T13”)
As you can tell, I am not a programmer

OR the simplest workaround is to just have another column (say T) where I place the code (I have this one working):

=If(S33>0,S33,””)

Again, thanks for viewing,
Steve