View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
DTM[_5_] DTM[_5_] is offline
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