| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Calculation Problem
I apologize in advance for re-posting this question. However, I haven't got
an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
I cut and pasted your code over the top of mine. It gives me an answer of 0
in the destination cell. I just don't get it???? -- Thanks Shawn "Bob Phillips" wrote: 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
I put J1 in A1, A in B1 and A in K1, and I got 1.
Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
I am sorry for being unclear. In the line:
Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
can we see some data you have entered in those cells
-- Gary "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
LOL. I originally thought it meant cell J1 but when I saw the extra quotes
in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
That did it and I could have swore that I tried that already, several times.
Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
Yep. That was easy <vbg
-- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
wouldn't this work, too, since he has the worksheeets variable set?
Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") -- Gary "Bob Phillips" wrote in message ... LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
Yes...that worked, too.
-- Thanks Shawn "Gary Keramidas" wrote: wouldn't this work, too, since he has the worksheeets variable set? Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") -- Gary "Bob Phillips" wrote in message ... LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
I tired the following code and it didn't work???
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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Bob Phillips" wrote: Yep. That was easy <vbg -- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
this worked for me using bob's values in his previous post, i changed it to
sheet 2 to see if it would work i get a 1 in h2 and the msgbox Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet2") Set Ans1 = WS.Range("h2") var1 = "J1" Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") MsgBox Ans1.Value End Sub -- Gary "Shawn" wrote in message ... I tired the following code and it didn't work??? 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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Bob Phillips" wrote: Yep. That was easy <vbg -- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
You didn't unload the userform did you (hide it instead)?
-- HTH Bob Phillips "Shawn" wrote in message ... I tired the following code and it didn't work??? 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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Bob Phillips" wrote: Yep. That was easy <vbg -- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
See the code below: I am now trying to get the formula to work but var1
reference the value in a textbox. ??? 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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Gary Keramidas" wrote: this worked for me using bob's values in his previous post, i changed it to sheet 2 to see if it would work i get a 1 in h2 and the msgbox Private Sub CommandButton1_Click() Dim Ans1 As Range Dim var1 As String Dim WS As Worksheet Set WS = Worksheets("Sheet2") Set Ans1 = WS.Range("h2") var1 = "J1" Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") MsgBox Ans1.Value End Sub -- Gary "Shawn" wrote in message ... I tired the following code and it didn't work??? 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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Bob Phillips" wrote: Yep. That was easy <vbg -- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
Yes...I hid it. I think I have to define this other than a string and write
the formula different???? -- Thanks Shawn "Bob Phillips" wrote: You didn't unload the userform did you (hide it instead)? -- HTH Bob Phillips "Shawn" wrote in message ... I tired the following code and it didn't work??? 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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Bob Phillips" wrote: Yep. That was easy <vbg -- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 ="" & var1 & "")*(B1:B10=k1))") End Sub -- Thanks Shawn |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Calculation Problem
No, either don't unload the form just hide it (Me.Hide), or dump the textbox
value to a public variable before you unload it. -- HTH Bob Phillips "Shawn" wrote in message ... Yes...I hid it. I think I have to define this other than a string and write the formula different???? -- Thanks Shawn "Bob Phillips" wrote: You didn't unload the userform did you (hide it instead)? -- HTH Bob Phillips "Shawn" wrote in message ... I tired the following code and it didn't work??? 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") var1 = UserForm1.TextBox1.Value Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))") End Sub -- Thanks Shawn "Bob Phillips" wrote: Yep. That was easy <vbg -- HTH Bob Phillips "Shawn" wrote in message ... That did it and I could have swore that I tried that already, several times. Maybe I just had typos. One more question, I suppose this would work as well if I had var1 assigned to a textbox value on a userform. Ie. var1 = txtbxOccupation.value -- Thanks Shawn "Bob Phillips" wrote: LOL. I originally thought it meant cell J1 but when I saw the extra quotes in your formula, I guessed you were trying to string it. How wrong could I be :-). Try this line Shawn. Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =" & var1 & ")*(B1:B10=K1))") Hopefully, this is it. I had 1,2,3 in A1:A3, a,b,c in B1:b3, and 2 in J1, b in K1, and I got 1 in H2 Fingers crossed! -- HTH Bob Phillips "Shawn" wrote in message ... I am sorry for being unclear. In the line: Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") The k1 references Worksheets("Sheet1").Range("K1"). The var1 variable should reference the cell address Worksheets("Sheet1").Range("J1"). I want to be able to name the cell address as variables in my sumproduct formula. I then want to use the variable name and the forumla function as if I had entered the cell address. -- Thanks Shawn "Bob Phillips" wrote: I put J1 in A1, A in B1 and A in K1, and I got 1. Am I mis-reading what you are trying to do with those values? -- HTH Bob Phillips "Bob Phillips" wrote in message ... 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") var1 = "J1" Ans1.Value = Worksheets("sheet1").Evaluate("=SUMPRODUCT((A1:A10 =""" & _ var1 & """)*(B1:B10=k1))") End Sub -- HTH Bob Phillips "Shawn" wrote in message ... I apologize in advance for re-posting this question. However, I haven't got an answer to it yet and thought a fresh string might catch some "new" eyes. I can't get the formula to calculate correctly. If I just put J1 in the formula, it does fine. However, I don't want to put J1, I want to put var1. This is vital to some significant programming I plan on doing, but have to make it work on the small scale first. Please help! 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") var1 = "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 | |||
| calculation problem | Excel Worksheet Functions | |||
| calculation problem | Excel Worksheet Functions | |||
| Calculation problem - please help | Excel Discussion (Misc queries) | |||
| conditional formatting vba code and calculation | Excel Programming | |||
| Calculation Problem | Excel Programming | |||
Linear Mode
