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
|