VBA Declaration problem
 

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

VBA Declaration problem
 

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

VBA Declaration problem
 

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