SUMPRODUCT last Column

[hanif]

Hi All,

I am using sumproducts in VBA and I have below code:

Dim i As Integer

lc = Sheets("sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
AIlc = Sheets("AI").Cells(2, Columns.Count).End(xlToLeft).Column

For i = 1 To 12

Cells(2 + i, lc + 1) = Application.Evaluate("SUMPRODUCT((MONTH(AI!$B$2:$B$366)=" & i & ")*(AI!$AY$2:$AY$366))")

Next i

instead of AI!$AY$2:$AY$366 in above code I want to refer to the last column of sheet AI. How can I change that in above code?

Thanks,

[hanif]

If anyone know any other way please say. I can upload the spreadsheet if helps understanding better.

[wayneg]

can you use a named range for the last column ?

[hanif]

I have defined the last column as "AIlc" and used it as instead of AI!$AY$2:$AY$366 but the return is VALUE or NAME.

can you use a named range for the last column ?

[hanif]

I have defined the last column as "AIlc" and used it as instead of AI!$AY$2:$AY$366 but the return is VALUE or NAME.

can you use a named range for the last column ?

[YasserKhalil]

Hello Hanif
Try something like that (Not Tested)

Sub Test()
    Dim I As Integer
    Dim lc As Long
    Dim AIlc As Long
    Dim str As String

    lc = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
    
    With Sheets("AI")
        AIlc = .Cells(2, Columns.Count).End(xlToLeft).Column
        str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address
    End With
    
    For I = 1 To 12
        Cells(2 + I, lc + 1) = Application.Evaluate("SUMPRODUCT((MONTH(AI!$B$2:$B$366)=" & I & ")*(AI!" & str & "))")
    Next I
End Sub

[hanif]

Thanks Yasser,

It does look very sensible. I was thinking the same idea but didn't know how to interprete it into vba. I am getting "Application-defined or object-defined error" message on below line. Any idea how can I fix that?

str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address

Thanks,

Hello Hanif
Try something like that (Not Tested)

Sub Test()
    Dim I As Integer
    Dim lc As Long
    Dim AIlc As Long
    Dim str As String

    lc = Sheets("Sheet2").Cells(3, Columns.Count).End(xlToLeft).Column
    
    With Sheets("AI")
        AIlc = .Cells(2, Columns.Count).End(xlToLeft).Column
        str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address
    End With
    
    For I = 1 To 12
        Cells(2 + I, lc + 1) = Application.Evaluate("SUMPRODUCT((MONTH(AI!$B$2:$B$366)=" & I & ")*(AI!" & str & "))")
    Next I
End Sub

[JOHN H. DAVIS]

Sure you didn't get the lower case "L" mixed up with the number 1?

str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address

They look alike when written.

[hanif]

Yes, I have double checked but still getting same error message!

Sure you didn't get the lower case "L" mixed up with the number 1?

str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address

They look alike when written.

[hanif]

Yes, I have double checked but still getting same error message!

Sure you didn't get the lower case "L" mixed up with the number 1?

str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address

They look alike when written.

[AB33]

Please do not quote when you reply! Just reply will do.
I have tested it and it works.
Also use Option Explicit with the code to spot out a typo

Sub Test()
Dim I As Integer, lc As Long, AIlc As Long, str As String, ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Sheets("AI")
    Set ws2 = Sheets("sheet2")
    lc = ws2.Cells(3, ws2.Columns.Count).End(xlToLeft).Column
    
    With Sheets("AI")
        AIlc = .Cells(2, Columns.Count).End(xlToLeft).Column
        str = .Range(.Cells(2, AIlc), .Cells(366, AIlc)).Address
    End With
    
    For I = 1 To 12
        ws2.Cells(2 + I, lc + 1) = Application.Evaluate("SUMPRODUCT((MONTH(AI!$B$2:$B$366)=" & I & ")*(AI!" & str & "))")
    Next I
End Sub

[JOHN H. DAVIS]

That's the only thing I see that would produce that error. What about in your Dim statement?

[hanif]

I have checked them all, they are all fine. AIlc gives me a value of 166248. Would this could cause the problem?

That's the only thing I see that would produce that error. What about in your Dim statement?