Consulting

Results 1 to 7 of 7

Thread: How to use Countif in VBA

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Regular
    Joined
    Jan 2006
    Posts
    10
    Location

    Lightbulb How to use Countif in VBA

    Hi,

    Can any one tell me that how to use Countif function in VBA (Macro Programming). I need to count the no.of rows with specifying the criteria. I can do this using for loop and but just want to know whether is it possible to use simple function rather than using loop and conditional statements.

    Thanks in Advance !

    Warm regards,
    Di$cover

  2. #2
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    There might be other ways - I'm not entirely sure what you want to do - but you can use Application.CountIf(Range("a1:a20"),"=5") for example
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  3. #3
    VBAX Regular
    Joined
    Jan 2006
    Posts
    10
    Location
    Thanks for the reply Tony.. but when i put this formula it is returning "0" value. my code is goes like this...
    Sheets("Daily Call Report").Range("C8").Select
    ActiveCell.FormulaR1C1 = Application.CountIf(Range("H1:H400"), "=17/05/2006")
    i want to count how many rows r there in a sheet which contains the date "17/05/2006".
    Thanks in Advance !

  4. #4
    Administrator
    Chat VP
    VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    Moved to Excel Help forum...
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  5. #5
    Administrator
    Chat VP VBAX Guru johnske's Avatar
    Joined
    Jul 2004
    Location
    Townsville, Australia
    Posts
    2,872
    Location
    You're better off using CountA for that...
    [vba]Option Explicit
    '
    Sub Try()
    With Sheets("Daily Call Report")
    .Range("C8") = Application.CountA(Range("H1:H400"), "=17/05/2006") - 1
    End With
    End Sub[/VBA]
    You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you

    The major part of getting the right answer lies in asking the right question...


    Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

  6. #6
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    probably a silly question but why do you want to use VBA to put the formula result in the cell rather than putting the formula itself in the cell?
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  7. #7
    VBAX Regular
    Joined
    Jan 2006
    Posts
    10
    Location
    Yes it is a silly question.. i know.. but the code what i sent is a just part of it. and more over this report is very big one.. and it includes only the count of no.s. so i thouhgt of creating a macro. Any way ... thanks for your help. i wil get back to you.. once i use this code in my program.

Posting Permissions

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