PDA

View Full Version : Solved: Sumproduct function with VBA



Shinu
08-01-2006, 12:25 AM
Dear sir,
I have 2 workbooks named "macros" and "ll2006". There are ranges defined in 2nd workbook.
I need to do some analysis in 1st workbook based on the data in 2nd workbook.

I am trying to write vba codes, but I dont know how to use the SUMPRODUCT function.

Formula :-

=sumproduct((MCORPN=c4)*(MCLASS=3))
The above formula I want to use in the vba codes. I dont know how to write it.
I tried writing the below given code but it is not working
Could you pls. help me witht this.



Sub linelist()
Set wb = Application.Workbooks("ll2006")
Set sh = wb.Sheets("ll") ' Sheet having the database records

' rc[-10] that is Range("C4") . It contains the same data as in MCORPN range

Range("M4").Value = _
Evaluate("=SUMPRODUCT((" & sh.Range("MCORPN") & "= rc[-10])*(" & sh.Range("MCLASS") & "=3))")

End sub

Bob Phillips
08-01-2006, 12:57 AM
Not tested but try this



Range("M4").Value = _
Evaluate("=SUMPRODUCT((" & sh.Range("MCORPN").Address(0, 0) & _
"=C4)*(" & sh.Range("MCLASS").Address(0, 0) & "=3))")

Shinu
08-01-2006, 01:43 AM
It shows error #VALUE! as result

jindon
08-01-2006, 01:47 AM
Get rid of first = sign in the bracket


Range("M4").Value = _
Evaluate("SUMPRODUCT((" & sh.Range("MCORPN").Address(0, 0) & _
"=C4)*(" & sh.Range("MCLASS").Address(0, 0) & "=3))")

Shinu
08-01-2006, 01:49 AM
It is still showing error #VALUE! as result

Bob Phillips
08-01-2006, 01:51 AM
It works fine for me. It looks to me that your ranges may have a #VALUE in them.

Shinu
08-01-2006, 01:54 AM
No sir, I have checked both range MCORPN and MCLASS. There are no #values

Shinu
08-01-2006, 02:00 AM
It is showing 0 as result. While the result should have been 13 as per the database.
Shall i attach the files

Bob Phillips
08-01-2006, 02:37 AM
It is showing 0 as result. While the result should have been 13 as per the database.
Shall i attach the files

Yes, do.

Shinu
08-01-2006, 03:24 AM
Dear sir,
I have attached the file for your reference.
In file ll2006
Column BP is defined as MCORPN
Column AN is defined as MCLASS
In file macros there is macro written named "check"

Thanks in advance
Shinu

Ken Puls
08-01-2006, 08:42 AM
Shinu,

If you are going to cross post (http://www.officearticles.com/forums/showthread.php?t=124) (post in multiple forums), please read this (http://www.excelguru.ca/node/7).

Bob Phillips
08-01-2006, 09:52 AM
Dear sir,
I have attached the file for your reference.
In file ll2006
Column BP is defined as MCORPN
Column AN is defined as MCLASS
In file macros there is macro written named "check"

Thanks in advance
Shinu

Your ranges are called HCORPN and HCLASS, not MCORPN and MCLASS, and they are hust the header cells, not a lot of chance of getting a result.

Bob Phillips
08-01-2006, 09:53 AM
Get rid of first = sign in the bracket


Range("M4").Value = _
Evaluate("SUMPRODUCT((" & sh.Range("MCORPN").Address(0, 0) & _
"=C4)*(" & sh.Range("MCLASS").Address(0, 0) & "=3))")


Not relevant.

Aaron Blood
08-01-2006, 10:26 AM
Just a point of interest...

For the example as stated, there's no need to concatenate the range.address as suggested; the formula can be stated exactly as it would appear in the cell.

Has nothing to do with why your formula isn't working though.



'instead of...
Range("M4").Value = _
Evaluate("SUMPRODUCT((" & sh.Range("MCORPN").Address(0, 0) & _
"=C4)*(" & sh.Range("MCLASS").Address(0, 0) & "=3))")

'This would work equally as well...
Range("M4").Value = Evaluate("SUMPRODUCT((MCORPN=C4)*(MCLASS=3))")

'and if there was no variable concatentation, then this would also suffice...
Range("M4").Value = [SUMPRODUCT((MCORPN=C4)*(MCLASS=3))]



I've equated the EVALUATE command to a virtual spreadsheet cell in the past...

So basically, if you eventually get the formula to work right in a cell, it'll work equally as well in VBA.

Shinu
08-01-2006, 10:37 PM
Sir,
Range("M4").Value = [SUMPRODUCT((MCORPN=C4)*(MCLASS=3))] formula dose not work since the names defined are in different file.
It shows error as #NAME? error

Pls. go through the attached file (Msg no:- 10) and let me know the solution.

To see the ranges of MCORPN and MCLASS click on Insert- Name-Define. I have used the offset function.

Thanks
Shinu

Bob Phillips
08-02-2006, 01:00 AM
Those range names do not exist in either workbook.

Shinu
08-02-2006, 02:31 AM
Dear sir,
The names do exist. I have checked it again. The names can only be seen by clicking on Insert-Name-Define and search for name MCLASS and MCORPN. You will surely find it.
These names exist in "ll2006" workbook only.
Thanks
Shinu

Bob Phillips
08-02-2006, 03:03 AM
Dear sir,
The names do exist. I have checked it again. The names can only be seen by clicking on Insert-Name-Define and search for name MCLASS and MCORPN. You will surely find it.
These names exist in "ll2006" workbook only.
Thanks
Shinu

My apologies, I obviously didn't look hard enough.

This code works



Sub check()

Set wb = Application.Workbooks("ll2006.xls")
Set sh = wb.Sheets("ll")
Workbooks("macros.xls").Activate
Sheets("MH Corpn").Select
Range("M4").Value = _
Evaluate("SUMPRODUCT((" & sh.Range("MCORPN").Address(0, 0, , True) & _
"=C4)*(" & sh.Range("MCLASS").Address(0, 0, , True) & "=3))")
End Sub


but why use VBA, why not put the formula in the cell.

Aaron Blood
08-02-2006, 07:19 AM
My apologies, I obviously didn't look hard enough.

This code works



Sub check()

Set wb = Application.Workbooks("ll2006.xls")
Set sh = wb.Sheets("ll")
Workbooks("macros.xls").Activate
Sheets("MH Corpn").Select
Range("M4").Value = _
Evaluate("SUMPRODUCT((" & sh.Range("MCORPN").Address(0, 0, , True) & _
"=C4)*(" & sh.Range("MCLASS").Address(0, 0, , True) & "=3))")
End Sub


but why use VBA, why not put the formula in the cell.


As I mentioned, you don't need to concact the Range.Address like that...

The issue revolves around not being explicit with the workbook and sheet name in the address. Selecting the WB and WS gets it done, just probably not the cleanest solution. You could probably concat your way around that one. If it were me, I'd scrap the whole concept of using SUMPRODUCT and go with a FIND method or my KBFF example.

Shinu
08-03-2006, 01:45 AM
Dear all,
Thanks for your valuable reply. My issues are solved.
Thanks a lot
Regards
Shinu

Bob Phillips
08-03-2006, 03:46 AM
Can you mark the thread as solved?

Shinu
08-03-2006, 04:08 AM
Could you pls. guide me for this

Bob Phillips
08-03-2006, 04:34 AM
I belive that it is on the Thread Tools dropdown.