| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Problem
Why is the code below giving me the wrong answer. In the formula line, if I
just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
"Shawn" wrote in message
... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
You had two answers to the same question yesterday.
-- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
I tired this code but get an error?
Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1").Address Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn "Rob Bovey" wrote: "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
None worked.
-- Thanks Shawn "Bob Phillips" wrote: You had two answers to the same question yesterday. -- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
What error?
-- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... I tired this code but get an error? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1").Address Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn "Rob Bovey" wrote: "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
A compile error on this line:
Set var1 = WS.Range("J1").Address -- Thanks Shawn "Bob Phillips" wrote: What error? -- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... I tired this code but get an error? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1").Address Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn "Rob Bovey" wrote: "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
Hi Shawn,
A compile error on this line: Set var1 = WS.Range("J1").Address Amend the offending line with: var1 = WS.Range("J1").Address The Set statement is used to assign an object reference to a variable. --- Regards, Norman "Shawn" wrote in message ... A compile error on this line: Set var1 = WS.Range("J1").Address -- Thanks Shawn "Bob Phillips" wrote: What error? -- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... I tired this code but get an error? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1").Address Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn "Rob Bovey" wrote: "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
Well I don't get the error but it is giving me the wrong answer. ???
-- Thanks Shawn "Norman Jones" wrote: Hi Shawn, A compile error on this line: Set var1 = WS.Range("J1").Address Amend the offending line with: var1 = WS.Range("J1").Address The Set statement is used to assign an object reference to a variable. --- Regards, Norman "Shawn" wrote in message ... A compile error on this line: Set var1 = WS.Range("J1").Address -- Thanks Shawn "Bob Phillips" wrote: What error? -- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... I tired this code but get an error? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1").Address Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn "Rob Bovey" wrote: "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VBA Code Problem
Hi Shawn,
What was the answer you wanted? --- Regards, Norman "Shawn" wrote in message ... Well I don't get the error but it is giving me the wrong answer. ??? -- Thanks Shawn "Norman Jones" wrote: Hi Shawn, A compile error on this line: Set var1 = WS.Range("J1").Address Amend the offending line with: var1 = WS.Range("J1").Address The Set statement is used to assign an object reference to a variable. --- Regards, Norman "Shawn" wrote in message ... A compile error on this line: Set var1 = WS.Range("J1").Address -- Thanks Shawn "Bob Phillips" wrote: What error? -- HTH RP (remove nothere from the email address if mailing direct) "Shawn" wrote in message ... I tired this code but get an error? Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1").Address Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn "Rob Bovey" wrote: "Shawn" wrote in message ... Why is the code below giving me the wrong answer. In the formula line, if I just use J1 I get the right answer. When I use var1 (which is set as J1), it gives the wrong answer. ??? Because you are trying to build a formula string but your var1 variable is a Range object. In this case VBA uses the default property of the Range object, which is its Value property. So what you are doing is the equivalent of: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1.Value & "")*(B1:B10=k1))") You need to declare var1 as a String and put the address of the cell you want to use in your formula into it. -- Rob Bovey, Excel MVP Application Professionals http://www.appspro.com/ * Take your Excel development skills to the next level. * Professional Excel Development http://www.appspro.com/Books/Books.htm Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As Range Dim WS As Worksheet Set WS = Worksheets("Sheet1") Set Ans1 = WS.Range("h2") Set var1 = WS.Range("J1") Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Code problem | Excel Discussion (Misc queries) | |||
| VB Code Problem | Excel Discussion (Misc queries) | |||
| Little problem with this code... | Excel Discussion (Misc queries) | |||
| Problem with Code Below | Excel Programming | |||
| Code problem | Excel Programming | |||
Linear Mode
