Thanks Jim, it is appreciated.

Regards

Bob


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Many many thanks - it works fine now

My apologies for the late acknowledgement ... I cut and pasted you piece
of
code and it took me a while to figure out why it didn't work
(I am using ComboBox2 not ComboBox1)
I also moved the code from the spin events to the TextBox4.Change event.

I, and many thousands of others, owe you and your colleagues in this group
a
great deal for the amount of time and effort you save us in solving these
vba problems. So much so that it often very tempting to ask before
spending
even attempting to solve them for ourselves.

Heartfelt thanks and many regards

Jim Burton
(SA3214)



"Bob Phillips" wrote in message
...
It is probably caused because you are comparing strings, and I didn't
allow
for this. If both the A and B columns are text, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=""" & Textbox4.Text & """),D1:D10))")

If only column A is text, and B is a number, use

TextBox11 = Evaluate("SUM(IF(((A1:A10)=""" & ComboBox1.Value & _
""")*((B1:B10)=" & CDbl(Textbox4.Text) & "),D1:D10))")

Hope that this sorts it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I'm still getting an error as follows:

Runtime error '-2147352571 (80020005)
Could not set the value property. Type mismatch

I have two sheets in the workbook Invoices and Payments
ComboBox2 contains a supplier name selected from a list of Invoices
(recorded on sheet 1)
Textbox4 contains an Invoice Number which is selected by using a
textbox
with an associated spinner
The spinup and spindown events cycle through the invoices for the
supplier
Each spinner event is supposed to display the sum of all payments made
against the invoice (recorded on sheet 2)
see code below

In desperation I have formated the cells containing the invoice number
as
both Text and Number on both sheets without having any effect on the
error

Any help would be appreciated
...........................................

Private Sub SpinButton1_SpinDown()
With Sheets("Invoices")
Set C = .Range("a:a").FindNext(C)
If C.Address = FirstAddress Then
Set C = .Range("a:a").FindPrevious(C)
LastAddress = C.Address
MsgBox "Last Invoice for " & ComboBox2
End If
TextBox4 = .Cells(C.Row, 3)
TextBox5 = .Cells(C.Row, 4)

With Sheets("Payments")
'next line gives error
TextBox11 = Evaluate("SUM(IF(((A1:A10)=" & ComboBox2.Value
&
")*((B1:B10)=" & TextBox4.Text & "),D1:D10))")
End With

End With
End Sub

"Bob Phillips" wrote in message
...
TextBox11 =
Evaluate("SUM(IF(((A1:A10)=" & Combobox2.Value & ")*((B1:B10)=" &
TextBox4.Text & "),D1:D10))")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
.... I am getting a type mismatch error again

I substituted ComboBox2 and Textbox4 for the criteria as follows:

TextBox11 =
Evaluate("SUM(IF(((A1:A10)=Combobox2)*((B1:B10)=Te xtBox4),D1:D10))")

and it falls over again .... any suggestions please



"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
Thank you for your input ... I was, as you rightly pointed out,
getting
the type mismatch error
Now I am using Evaluate it works correctly

Regards to you all and many thanks for your help
................

"Bob Phillips" wrote in
message
...
Hi Jim,

In VBA, you cannot use worksheet ranges, you need to specify the
object
(I
know that you know, but the OP may not). SO at least you need


txtMyTextBox.value =



application.SUMPRODUCT(--(Range("A1:A1000")="val1"),--(Range("B1:B1000")="va
l2"),Range("C1:C1000"))

but this just throws a type mismatch. VBA doesn't like the --
operator,
and
even using * doesn't fix it.

I have found only evaluate works with SUMPRODUCT in VBA, and that
works
okay
with your original as well.

Regards

Bob


"Jim Thomlinson" wrote
in
message
...
Here you want to use the sumproduct that Bob or Tom indicated.

txtMyTextBox.value =


application.SUMPRODUCT(--(A1:A1000="val1"),--(B1:B1000="val2"),C1:C1000)

I have not tested it but that should be it...

HTH

"SA3214 @Eclipse.co.uk" wrote:

Thanks to all for your input.

Now I need to do the same thing using vba to display the total
in
a
textbox

Can I use a WorksheetFunction to achieve this ?




"Jim Thomlinson"
wrote
in
message
...
Very Possible to do. You want an array formula. The big
trick
here
is
the
and. In an array formula and is represented by * and or is
represented
by
^.
So the formula you want is something like this...

=SUM(IF(((A1:A10)="This")*((B1:B10)="That"), C1:C10))

Note that to enter an ary formula you need to hit
Shift+Ctrl+Enter
instead
of just enter. The formula will be displayed with curly{}
braces
around it
when you do...

HTH

"SA3214 @Eclipse.co.uk" wrote:

Is it possible to sum a range of cells based on two
criteria
eg Sum of cells in Column(C) ... if Column(A)=string1 and
Column(B)
=
string2

Hope you can understand that


Regards and TIA