Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Sumproduct in VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default Sumproduct in VBA
I am trying to get round the problems using sumproduct in VBA and have
made quite a few changes using tips I have found, this includes
changing to Application.Evaluate("....

Can anyone see where I am going wrong

Public Sub SpecificPosCalc()

Dim BED As Double
Dim BUC As Double
Dim BUJ As Double
Dim BGU As Double
Dim BEJ As Double
Dim BAU As Double
Dim BDC As Double
Dim SED As Double
Dim SUC As Double
Dim SUJ As Double
Dim SGU As Double
Dim SEJ As Double
Dim SAU As Double
Dim SDC As Double
Set trade = Range("c2:c3000")
Set cur1 = Range("d2:d3000")
Set cur2 = Range("f2:f3000")
Set vol = Range("e2:e3000")
E = "EUR"
D = "USD"
G = "GBP"
J = "JPY"
C = "CAD"
A = "AUD"
F = "CHF"
B = "BUY"
S = "SELL"

BED = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
BUC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & F & "))")
BUJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
BGU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & G & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
BEJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
BAU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & A & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
BDC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & C & "))")
SED = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
SUC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & F & "))")
SUJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
SGU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & G & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
SEJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
SAU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & A & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
SDC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & C & "))")

T = BED - BUC

MsgBox "Your position is: " & T & "." 'not complete but you get the
point

End Sub

Thanks in advance for any help

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default Sumproduct in VBA
Public Sub SpecificPosCalc()

Dim BED As Double
Dim BUC As Double
Dim BUJ As Double
Dim BGU As Double
Dim BEJ As Double
Dim BAU As Double
Dim BDC As Double
Dim SED As Double
Dim SUC As Double
Dim SUJ As Double
Dim SGU As Double
Dim SEJ As Double
Dim SAU As Double
Dim SDC As Double
Dim trade, cur1, cur2, vol
Dim E, D, G, J, C, A, F, B, S, T
Set trade = Range("c2:c3000")
Set cur1 = Range("d2:d3000")
Set cur2 = Range("f2:f3000")
Set vol = Range("e2:e3000")
E = "EUR"
D = "USD"
G = "GBP"
J = "JPY"
C = "CAD"
A = "AUD"
F = "CHF"
B = "BUY"
S = "SELL"

BED = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & E & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & D & """))")
BUC = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & D & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & F & """))")
BUJ = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & D & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & J & """))")
BGU = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & G & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & D & """))")
BEJ = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & E & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & J & """))")
BAU = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & A & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & D & """))")
BDC = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & B & """)*(" & cur1.Address & "=""" & D & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & C & """))")
SED = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & E & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & D & """))")
SUC = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & D & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & F & """))")
SUJ = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & D & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & J & """))")
SGU = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & G & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & D & """))")
SEJ = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & E & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & J & """))")
SAU = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & A & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & D & """))")
SDC = Application.Evaluate("SumProduct((" & trade.Address & _
"=""" & S & """)*(" & cur1.Address & "=""" & D & """)*(" & _
vol.Address & ")*(" & cur2.Address & "=""" & C & """))")

T = BED - BUC

MsgBox "Your position is: " & T & "." 'not complete but you get the Point

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"DTM" wrote in message
oups.com...
I am trying to get round the problems using sumproduct in VBA and have
made quite a few changes using tips I have found, this includes
changing to Application.Evaluate("....

Can anyone see where I am going wrong

Public Sub SpecificPosCalc()

Dim BED As Double
Dim BUC As Double
Dim BUJ As Double
Dim BGU As Double
Dim BEJ As Double
Dim BAU As Double
Dim BDC As Double
Dim SED As Double
Dim SUC As Double
Dim SUJ As Double
Dim SGU As Double
Dim SEJ As Double
Dim SAU As Double
Dim SDC As Double
Set trade = Range("c2:c3000")
Set cur1 = Range("d2:d3000")
Set cur2 = Range("f2:f3000")
Set vol = Range("e2:e3000")
E = "EUR"
D = "USD"
G = "GBP"
J = "JPY"
C = "CAD"
A = "AUD"
F = "CHF"
B = "BUY"
S = "SELL"

BED = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
BUC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & F & "))")
BUJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
BGU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & G & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
BEJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
BAU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & A & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
BDC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& B & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & C & "))")
SED = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
SUC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & F & "))")
SUJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
SGU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & G & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
SEJ = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & E & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & J & "))")
SAU = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & A & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & D & "))")
SDC = Application.Evaluate("SumProduct((" & trade.Address & " = "
& S & ") * (" & cur1.Address & " = " & D & ") * (" & vol.Address & ")
* (" & cur2.Address & " = " & C & "))")

T = BED - BUC

MsgBox "Your position is: " & T & "." 'not complete but you get the
point

End Sub

Thanks in advance for any help



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE gholly Excel Discussion (Misc queries) 2 September 28th 09 05:07 PM
sumproduct - < Rick Excel Discussion (Misc queries) 5 July 5th 09 03:45 PM
Conditional SUMPRODUCT or SUMPRODUCT with Filters Ted M H Excel Worksheet Functions 4 August 14th 08 07:50 PM
sumproduct? sumif(sumproduct)? David Excel Worksheet Functions 3 July 13th 07 07:06 PM
SUMPRODUCT help again! TMF in MN Excel Worksheet Functions 4 February 22nd 06 08:01 PM


All times are GMT +1. The time now is 05:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement