I am pretty new to declaring variables etc. Generally, I have let VBA use
defaults and not worried about declaring anything, but in an effort to reduce
file sizes and increase speeds I am expiermenting. What is wrong with the
code below? When I didn't define Var1 and just used J1 in the formula, it
worked fine. ???


Private Sub CommandButton1_Click()

Dim Ans As Range
Dim Var1 As Range

Set Ans = Worksheets("Sheet1").Range("h2")
Set Var1 = Worksheets("Sheet1").Range("J1")


Ans.Value =
Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =Var1)*(B1:B10=k1)*(C1:C10=L1)*(D1:F102))")

End Sub
--
Thanks
Shawn

The Var1 variable is recognized within the scope of the sub procedure but not
to Excel. Suggested is that you splice it into the statement by concatenation:

Private Sub CommandButton1_Click()
Dim Ans As Range
Dim Var1 As Range
Dim ws As Worksheet

Set ws = Sheets("Sheet1")
Set Ans = ws.Range("H2")
Set Var1 = ws.Range("J1")

Ans.Value = _
Application.Evaluate("=SUMPRODUCT((A1:A10=" & Var1 &
")*(B1:B10=k1)*(C1:C10=L1)*(D1:D102))")
End Sub

Regards,
Greg

"Shawn" wrote:

I am pretty new to declaring variables etc. Generally, I have let VBA use
defaults and not worried about declaring anything, but in an effort to reduce
file sizes and increase speeds I am expiermenting. What is wrong with the
code below? When I didn't define Var1 and just used J1 in the formula, it
worked fine. ???


Private Sub CommandButton1_Click()

Dim Ans As Range
Dim Var1 As Range

Set Ans = Worksheets("Sheet1").Range("h2")
Set Var1 = Worksheets("Sheet1").Range("J1")


Ans.Value =
Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =Var1)*(B1:B10=k1)*(C1:C10=L1)*(D1:F102))")

End Sub
--
Thanks
Shawn

The Evaluate evaluates a string, and you have embedded the objects in them.
It wouldn't work with or without declaring the variables. You need


Ans.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & Var1 &
")*(B1:B10=k1)*(C1:C10=L1)*(D1:F102))")

or maybe


Ans.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & Var1 &
""")*(B1:B10=k1)*(C1:C10=L1)*(D1:F102))")

if the test value is a string

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shawn" wrote in message
...
I am pretty new to declaring variables etc. Generally, I have let VBA use
defaults and not worried about declaring anything, but in an effort to
reduce
file sizes and increase speeds I am expiermenting. What is wrong with the
code below? When I didn't define Var1 and just used J1 in the formula, it
worked fine. ???


Private Sub CommandButton1_Click()

Dim Ans As Range
Dim Var1 As Range

Set Ans = Worksheets("Sheet1").Range("h2")
Set Var1 = Worksheets("Sheet1").Range("J1")


Ans.Value =

Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =Var1)*(B1:B10=k1)*(C1:C10
=L1)*(D1:F102))")

End Sub
--
Thanks
Shawn