Evaluate using a variable - Help please
 

I am having some difficulty substituting a variable (LastPRow) in place of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would work
.... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) & "),D1:D10))"),
"#,###.00")
End With

Evaluate using a variable - Help please
 

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I am having some difficulty substituting a variable (LastPRow) in place of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would work
... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) & "),D1:D10))"),
"#,###.00")
End With



Why do you use CDbl(TextBox4.Text) ? It is already text.If you want to
replace 20 with LastPRow do this

TextBox11 = Format(Evaluate("SUM(IF(((A2:A" & Cstr(LastPRow) & ")=""" &
ComboBox2.Value & """)*((B2:B" & Cstr(LastPRow) & ")=" &
CDbl(TextBox4.Text) & "),D1:D10))"), "#,###.00")

/Fredrik

Evaluate using a variable - Help please
 

try this idea
myRng = Range("a2:a" & Cells(Rows.Count, "A").End(xlUp).Row)

MsgBox Application.Sum(myRng)

--
Don Guillett
SalesAid Software

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I am having some difficulty substituting a variable (LastPRow) in place of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would work
... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) & "),D1:D10))"),
"#,###.00")
End With

Evaluate using a variable - Help please
 

You use rows 2:20 in most of your formula. But the last reference is D1:D10. I
bet you wanted D2:D20 (or D1:d19???).


With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11.Text = Format(Evaluate("SUM(IF(((A2:A" & LastPRow & ")=""" & _
ComboBox2.Value & """)*((B2:B" & LastPRow & ")=" _
& CDbl(TextBox4.Text) & "),D2:D" & LastPRow & "))"), _
"#,###.00")
End With

compiled for me and worked when the combobox2 and textbox4 were valid.


"SA3214

I am having some difficulty substituting a variable (LastPRow) in place of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would work
... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) & "),D1:D10))"),
"#,###.00")
End With

--

Dave Peterson

Evaluate using a variable - Help please
 

"Fredrik Wahlgren" wrote in message
...

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I am having some difficulty substituting a variable (LastPRow) in place
of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would
work
... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) & "),D1:D10))"),
"#,###.00")
End With



Why do you use CDbl(TextBox4.Text) ? It is already text.If you want to
replace 20 with LastPRow do this

TextBox11 = Format(Evaluate("SUM(IF(((A2:A" & Cstr(LastPRow) & ")=""" &
ComboBox2.Value & """)*((B2:B" & Cstr(LastPRow) & ")=" &
CDbl(TextBox4.Text) & "),D1:D10))"), "#,###.00")

/Fredrik


1) CDbl(TextBox4.Text) .... This was left over from previous attempts to
avoid the mismatch error I have removed the conversion and (as you implied)
it makes no difference.

2) However substituting " & Cstr(LastPRow) & " for 20 still gives me the
Mismatch error

There was also an error in my original post but I don't think it affects the
mismatch ... D10 should have been D20

Evaluate using a variable - Help please
 

Oh, I think that d20 may have something to say!

"SA3214

"Fredrik Wahlgren" wrote in message
...

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I am having some difficulty substituting a variable (LastPRow) in place
of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would
work
... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) & "),D1:D10))"),
"#,###.00")
End With



Why do you use CDbl(TextBox4.Text) ? It is already text.If you want to
replace 20 with LastPRow do this

TextBox11 = Format(Evaluate("SUM(IF(((A2:A" & Cstr(LastPRow) & ")=""" &
ComboBox2.Value & """)*((B2:B" & Cstr(LastPRow) & ")=" &
CDbl(TextBox4.Text) & "),D1:D10))"), "#,###.00")

/Fredrik


1) CDbl(TextBox4.Text) .... This was left over from previous attempts to
avoid the mismatch error I have removed the conversion and (as you implied)
it makes no difference.

2) However substituting " & Cstr(LastPRow) & " for 20 still gives me the
Mismatch error

There was also an error in my original post but I don't think it affects the
mismatch ... D10 should have been D20

--

Dave Peterson

Evaluate using a variable - Help please - Thanks for your help
 

"Dave Peterson" wrote in message
...
You use rows 2:20 in most of your formula. But the last reference is
D1:D10. I
bet you wanted D2:D20 (or D1:d19???).

Snip.....

Yes .... you are correct ... it should have been D20 ... Now corrected but
makes no difference to the mismatch error

..... end snip

Next snip ...
With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11.Text = Format(Evaluate("SUM(IF(((A2:A" & LastPRow & ")="""
& _
ComboBox2.Value & """)*((B2:B" & LastPRow & ")=" _
& CDbl(TextBox4.Text) & "),D2:D" & LastPRow & "))"), _
"#,###.00")
End With

compiled for me and worked when the combobox2 and textbox4 were valid.
.... end snip

Works for me too ... wonder what I was doing wrong

My sincere thanks to you all
What a wonderful resource this group is
Long may it flourish

Evaluate using a variable - Help please
 

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...

"Fredrik Wahlgren" wrote in
message
...

"SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message
...
I am having some difficulty substituting a variable (LastPRow) in place
of
the Number 20 in the code below.
I assumed that if I substituted " & LastPRow & " for 20 it would
work
... but it doesn't.
It returns Error 13, Type Mismatch

TIA for any help you can provide

vba code ..........

With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11 = Format(Evaluate("SUM(IF(((A2:A20)=""" &
ComboBox2.Value & """)*((B2:B20)=" & CDbl(TextBox4.Text) &
"),D1:D10))"),
"#,###.00")
End With



Why do you use CDbl(TextBox4.Text) ? It is already text.If you want to
replace 20 with LastPRow do this

TextBox11 = Format(Evaluate("SUM(IF(((A2:A" & Cstr(LastPRow) & ")=""" &
ComboBox2.Value & """)*((B2:B" & Cstr(LastPRow) & ")=" &
CDbl(TextBox4.Text) & "),D1:D10))"), "#,###.00")

/Fredrik


1) CDbl(TextBox4.Text) .... This was left over from previous attempts to
avoid the mismatch error I have removed the conversion and (as you
implied)
it makes no difference.

2) However substituting " & Cstr(LastPRow) & " for 20 still gives me the
Mismatch error

There was also an error in my original post but I don't think it affects
the
mismatch ... D10 should have been D20



I think you should try a simpler example and when that works, gradually make
it more complex
/Fredrik

Evaluate using a variable - Help please - Thanks for your help
 

What's in Textbox4? Is it numeric?

My bet is that it's not.

In my simple testing, if textbox4 was empty, it would cause this error, too.

"SA3214

"Dave Peterson" wrote in message
...
You use rows 2:20 in most of your formula. But the last reference is
D1:D10. I
bet you wanted D2:D20 (or D1:d19???).

Snip.....

Yes .... you are correct ... it should have been D20 ... Now corrected but
makes no difference to the mismatch error

.... end snip

Next snip ...
With Sheets("Payments")
LastPRow = .Cells(65536, 1).End(xlUp).Row
TextBox11.Text = Format(Evaluate("SUM(IF(((A2:A" & LastPRow & ")="""
& _
ComboBox2.Value & """)*((B2:B" & LastPRow & ")=" _
& CDbl(TextBox4.Text) & "),D2:D" & LastPRow & "))"), _
"#,###.00")
End With

compiled for me and worked when the combobox2 and textbox4 were valid.
.... end snip

Works for me too ... wonder what I was doing wrong

My sincere thanks to you all
What a wonderful resource this group is
Long may it flourish

--

Dave Peterson

Evaluate using a variable - Help please - Thanks for your help
 

Yes - I think you are put your finger on it ...
I've not been able to undo all the changes I've made in order to replicate
the situation.
I am somewhat confused by the need to convert entries in textboxes to
numeric especially when comparing them to entries in a spreadsheet
(I'm still very new to vba and inexperienced ... I also tend to write the
code on the fly without a program flow diagram or a functional spec)
I do have some reference books and use the vba help whenever I can but I
think I need a more structured course in programming

Thank you for your help and guidance





"Dave Peterson" wrote in message
...
What's in Textbox4? Is it numeric?

My bet is that it's not.

In my simple testing, if textbox4 was empty, it would cause this error,
too.

"SA3214

Evaluate using a variable - Help please - Thanks for your help
 

You can see the difference even on a worksheet:

Put 123 in A1
Put '123 in B1
put =a1=b1
in C1.

The apostrophe makes the value text. And text numbers are different from number
numbers.



"SA3214

Yes - I think you are put your finger on it ...
I've not been able to undo all the changes I've made in order to replicate
the situation.
I am somewhat confused by the need to convert entries in textboxes to
numeric especially when comparing them to entries in a spreadsheet
(I'm still very new to vba and inexperienced ... I also tend to write the
code on the fly without a program flow diagram or a functional spec)
I do have some reference books and use the vba help whenever I can but I
think I need a more structured course in programming

Thank you for your help and guidance

"Dave Peterson" wrote in message
...
What's in Textbox4? Is it numeric?

My bet is that it's not.

In my simple testing, if textbox4 was empty, it would cause this error,
too.

"SA3214

--

Dave Peterson

Evaluate using a variable - Help please - Thanks for your help
 

I understand that OK ...
I think my problem was that when I enter a number in a textbox I assumed
that excel would treat it as a number as it would if I entered it into a
cell ...
I guess I should try to remember that a textbox "does what it says on the
tin"


"Dave Peterson" wrote in message
...
You can see the difference even on a worksheet:

Put 123 in A1
Put '123 in B1
put =a1=b1
in C1.

The apostrophe makes the value text. And text numbers are different from
number
numbers.



"SA3214

Yes - I think you are put your finger on it ...
I've not been able to undo all the changes I've made in order to
replicate
the situation.
I am somewhat confused by the need to convert entries in textboxes to
numeric especially when comparing them to entries in a spreadsheet
(I'm still very new to vba and inexperienced ... I also tend to write the
code on the fly without a program flow diagram or a functional spec)
I do have some reference books and use the vba help whenever I can but I
think I need a more structured course in programming

Thank you for your help and guidance

"Dave Peterson" wrote in message
...
What's in Textbox4? Is it numeric?

My bet is that it's not.

In my simple testing, if textbox4 was empty, it would cause this error,
too.

"SA3214

--

Dave Peterson