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
For my current range I'm getting a type mismatch error.
Using the Immediate window to debug:
I have put the formulas into Excel and Excel returns the expected result of zero for both.
Any ideas??
/Comfy
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
Code:
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:
Code:
?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
Last edited: