Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Sep 2006
    Location
    Quilmes, Argentina
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Executing an Excel Formula in VBA code (2000)

    Hi all!
    I'm trying to do the following:
    <code>
    Sub example()

    dim strQuery as string
    dim intCount as integer

    strQuery = "COUNTIF(A4:A500;""=anyvalue"")"
    intCount=Excel.evaluate(strQuery)

    End Sub
    </code>

    My problem is that i cant execute the formula to get the result and use it later in the code!
    Any suggestions??

    Thanks in advance!!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Executing an Excel Formula in VBA code (2000)

    Have you tried:
    <code>strQuery = "COUNTIF(A4:A500,""=anyvalue"")"</code>
    using a comma rather than semicolon as the argument separator? (I'm assuming 'anyvalue' is the literal value?)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Executing an Excel Formula in VBA code (2000)

    You can also use

    intCount = Application.WorksheetFunction.CountIf(Range("A4:A5 00"),"anyvalue")

  4. #4
    New Lounger
    Join Date
    Sep 2006
    Location
    Quilmes, Argentina
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Executing an Excel Formula in VBA code (2000)

    Thank you Rory!!.. I always have this kind of mistakes!!!
    Regards!!

Posting Permissions

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