Vba evaluate

[Comfy]

Hi Boardies,

I'm having a little issue with EVALUATE() that I can't figure out.

I'm testing to see if a selection SUMS to zero and that the same rows in a different column have numbers in them

If Evaluate("ROUND(SUM(" & r.Address & "), 2)") = 0 And Evaluate("COUNT(" & Replace(r.Address, ConvertToLetter(r.Column), ConvertToLetter(LO1.ListColumns("MATCH ID").Range.Column)) & ")") = 0 Then

For my current range I'm getting a type mismatch error.

Using the Immediate window to debug:

?Evaluate("ROUND(SUM(" & r.Address & "), 2)")
Error 2015


?Evaluate("SUM(" & r.Address & ")")
 0 


?r.Address
$R$281:$R$282,$R$287:$R$288,$R$295:$R$296,$R$4708:$R$4711,$R$4714:$R$4715,$R$4718:$R$4719,$R$4724:$R$4727,$R$4730:$R$4731,$R$4734:$R$4735,$R$4738:$R$4739,$R$4743:$R$4744,$R$4747:$R$4748,$R$4751:$R$4752,$R$4788:$R$4789,$R$4792:$R$4793,$R$4795:$R$4796


?Evaluate("ROUND(SUM($R$281:$R$282,$R$287:$R$288,$R$295:$R$296,$R$4708:$R$4711,$R$4714:$R$4715,$R$4718:$R$4719,$R$4724:$R$4727,$R$4730:$R$4731,$R$4734:$R$4735,$R$4738:$R$4739,$R$4743:$R$4744,$R$4747:$R$4748,$R$4751:$R$4752,$R$4788:$R$4789,$R$4792:$R$4793,$R$4795:$R$4796), 2)")
Error 2015


?Evaluate("SUM($R$281:$R$282,$R$287:$R$288,$R$295:$R$296,$R$4708:$R$4711,$R$4714:$R$4715,$R$4718:$R$4719,$R$4724:$R$4727,$R$4730:$R$4731,$R$4734:$R$4735,$R$4738:$R$4739,$R$4743:$R$4744,$R$4747:$R$4748,$R$4751:$R$4752,$R$4788:$R$4789,$R$4792:$R$4793,$R$4795:$R$4796)")
 0 


?Evaluate("COUNT(" & Replace(r.Address, ConvertToLetter(r.Column), ConvertToLetter(LO1.ListColumns("MATCH ID").Range.Column)) & ")")
Error 2015


?Replace(r.Address, ConvertToLetter(r.Column), ConvertToLetter(LO1.ListColumns("MATCH ID").Range.Column))
$AB$281:$AB$282,$AB$287:$AB$288,$AB$295:$AB$296,$AB$4708:$AB$4711,$AB$4714:$AB$4715,$AB$4718:$AB$4719,$AB$4724:$AB$4727,$AB$4730:$AB$4731,$AB$4734:$AB$4735,$AB$4738:$AB$4739,$AB$4743:$AB$4744,$AB$4747:$AB$4748,$AB$4751:$AB$4752,$AB$4788:$AB$4789,$AB$4792:$AB$4793,$AB$4795:$AB$4796


?Evaluate("COUNT($AB$281:$AB$282,$AB$287:$AB$288,$AB$295:$AB$296,$AB$4708:$AB$4711,$AB$4714:$AB$4715,$AB$4718:$AB$4719,$AB$4724:$AB$4727,$AB$4730:$AB$4731,$AB$4734:$AB$4735,$AB$4738:$AB$4739,$AB$4743:$AB$4744,$AB$4747:$AB$4748,$AB$4751:$AB$4752,$AB$4788:$AB$4789,$AB$4792:$AB$4793,$AB$4795:$AB$4796)")
Error 2015

?IsNumeric(Evaluate("SUM($R$281:$R$282,$R$287:$R$288,$R$295:$R$296,$R$4708:$R$4711,$R$4714:$R$4715,$R$4718:$R$4719,$R$4724:$R$4727,$R$4730:$R$4731,$R$4734:$R$4735,$R$4738:$R$4739,$R$4743:$R$4744,$R$4747:$R$4748,$R$4751:$R$4752,$R$4788:$R$4789,$R$4792:$R$4793,$R$4795:$R$4796)"))
True

I have put the formulas into Excel and Excel returns the expected result of zero for both.

Any ideas??

/Comfy

 

[Comfy]

Sorry, as often happens I post a question and then continue playing and then possibly find the answer.

I think it might be due to the string length being used for the address.

/Comfy

 

[RoryA]

The formula string is limited to 255 characters.