Results 1 to 3 of 3

Thread: Vba evaluate
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vba evaluate

    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

    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 by Comfy; Nov 20th, 2018 at 11:27 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vba evaluate

    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

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,993
    Post Thanks / Like
    Mentioned
    53 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Vba evaluate

    The formula string is limited to 255 characters.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •