PDA

View Full Version : Sumproduct Function Help



Nader
03-05-2008, 09:25 AM
Here is a Sumproduct Function

"=SUMPRODUCT(--(Sheet2!$A$1:$A$10=A1)

I want to replace in this Function the name of Sheet2 by Worksheets(Label1.Caption) ,and make the column start from A10 to the End of column consideration may be it will work on the microssoft 2007.

Bob Phillips
03-05-2008, 09:55 AM
Is this a worksheet formula, or are you generating it in VBA?

Do you really meanstart at A10, or do you mean A1? If A10, is there anything in A1:A9?

What does this mean ... may be it will work on the microssoft 2007?

Nader
03-05-2008, 12:24 PM
This from your Replay

Dim LastRow3 As Long

With Worksheets("Sheet3")

LastRow3 = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

.Range("H1").Resize(LastRow3).Formula = "=SUMPRODUCT(--(Sheet2!$A$1:$A$10=A1),--(Sheet2!$B$1:$B$10=B1),--(Sheet2!$C$1:$C$10=C1),--(Sheet2!$D$1:$D$10=D1),Sheet2!$H$1:$H$10)"
End With

And here is the link:http://www.vbaexpress.com/forum/showthread.php?t=17776

- I want to start the dealing of the Function with sheet2 from A10 to end
Why microsoft2007 ... Because I don't want like this (A10...A65536).
I hope I'm clear.

Bob Phillips
03-05-2008, 12:33 PM
No way I can test it



Dim LastRow3 As Long
Dim LastRow4 As Long

With Worksheets(Label1.Caption)
LastRow4 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With Worksheets("Sheet3")

LastRow3 = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

.Range("H1").Resize(LastRow3).Formula = "=SUMPRODUCT(--('" & Label1.Caption & "'!$A$10:$A$" & LastRow4 & "=A1)," & _
"--('" & Label1.Caption & "'!$B$10:$B$" & LastRow4 & "=B1)," & _
"--('" & Label1.Caption & "'!$C$10:$C$" & LastRow4 & "=C1)," & _
"--('" & Label1.Caption & "'!$D$10:$D$" & LastRow4 & "=D1)," & _
"--('" & Label1.Caption & "'!$H$10:$H$" & LastRow4 & ")"
End With

Nader
03-05-2008, 10:44 PM
Thank you very mush XLD .

But it show me this run time error 1004
Aplication - defined or object-defined error.

Bob Phillips
03-06-2008, 02:20 AM
Post your workbook?

Nader
03-06-2008, 06:57 AM
Here is the code.

Bob Phillips
03-06-2008, 07:28 AM
An extra parentheses.

.Range("H1").Resize(LastRow3).Formula = "=SUMPRODUCT(--('" & Label1.Caption & "'!$A$1:$A$" & LastRow4 & "=A1)," & _
"--('" & Label1.Caption & "'!$B$1:$B$" & LastRow4 & "=B1)," & _
"--('" & Label1.Caption & "'!$C$1:$C$" & LastRow4 & "=C1)," & _
"--('" & Label1.Caption & "'!$D$1:$D$" & LastRow4 & "=D1)," & _
"--'" & Label1.Caption & "'!$H$1:$H$" & LastRow4 & ")"

Nader
03-06-2008, 10:16 AM
Ii succeed so may tell me.
What did you do ??..