Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    How to get a count of cells with formulas (2000/XP)

    I'm trying to get a count of the number of cells on a worksheet that use formulas. Each variation of the "SpecialCells(xlCellTypeFormulas,23).count" I've tried always results in an error if there are no cells with formulas. I'm trying to get a count of 0 instead of an error. I know I could test for the error but I just can't believe there isn't a better way.

    Thoughts?

    Thanks
    Ed

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to get a count of cells with formulas (2000/XP)

    You can loop through them like this (further testing required):

    Dim rngCell as Range
    Dim lngCounter as Long
    lngCounter = 0
    For Each rngCell in Selection ' or whatever
    If rngCell.HasFormula Then lngCounter = lngCounter +1
    Next rngCell
    MsgBox lngCounter

    Are you intending to write this as a Function?
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to get a count of cells with formulas (2000/XP)

    Dang...got beat to the answer.

    <pre>Sub CountFormulas()
    Dim Cell
    Dim i

    Range("A1:B10").Select
    For Each Cell In Selection
    If Cell.HasFormula Then
    i = i + 1
    End If
    Next Cell
    MsgBox i
    End Sub
    </pre>


  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: How to get a count of cells with formulas (2000/XP)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> happens to me all the time!
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to get a count of cells with formulas (2000/XP)

    If you have a lot of formulas, using SpecialCells would be quickest. Combine it with On Error as follows :

    On Error Resume Next
    MsgBox Cells.SpecialCells(xlCellTypeFormulas, 23).Count
    On Error GoTo 0

    Andrew C

Posting Permissions

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