Code Calculation Problem

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

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

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

 

[Bob Phillips]

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

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

 

[Guest]

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

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?

 

[Gary Keramidas]

can we see some data you have entered in those cells

 

[Bob Phillips]

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!

 

[Guest]

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

 

[Bob Phillips]

Yep. That was easy <vbg>

--
HTH

Bob Phillips

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

 

[Gary Keramidas]

wouldn't this work, too, since he has the worksheeets variable set?

Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))")

 

[Guest]

Yes...that worked, too.
--
Thanks
Shawn

wouldn't this work, too, since he has the worksheeets variable set?

Ans1.Value = WS.Evaluate("=SUMPRODUCT((A1:A10=" & var1 & ")*(B1:B10=K1))")

 

[Guest]

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

 

[Gary Keramidas]

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

 

[Bob Phillips]

You didn't unload the userform did you (hide it instead)?

 

[Guest]

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

 

[Guest]

Yes...I hid it. I think I have to define this other than a string and write
the formula different????

 

[Bob Phillips]

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.