Sumproduct in VBA

D

DTM

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
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMPRODUCT and SUMIFS combined with Sumifbycolor (VBA code) 1
VBA Sumproduct for External Source 3
Another rascally VBA SUMPRODUCT issue 2
Code only works in Debug mode. Why? 6
SUMPRODUCT IN VBA 1
Sumproduct() - Need for speed or change 1
VBA Sum Product 6
Sumproduct in VBA Type Mismatch problem 12

Top