EXCEL VBA - Miscalculation

TheWorldOfAlgorithms 0 Reputation points
2023-07-16T07:30:15.0233333+00:00

Hi!.

I have been using your products for many years, especially Excel and Excel VBA. What you are doing is amazing. I would like to express my thanks for this. My question is about Excel VBA. There is a discrepancy, I would like you to comment on it. A simple macro I wrote is as follows:

Sub CountFormulas()

Dim rng As Range, formula_cnt As Long

Selection.SpecialCells(xlCellTypeFormulas, 21).Select

For Each rng In Selection

formula_cnt = formula_cnt + 1

Next rng

MsgBox formula_cnt

End Sub

 

The problem is that this does not always reflect the correct result. Can you explain exactly what the problem is ? For example, when you write a formula based on RANDARRAY, it shows a completely wrong result:

User's image

When I run the macro, the result is 1, when the result should be 20.

Thank you in advance.

Sincerely,

TheWorldOfAlgorithms

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
2,174 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
4,273 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 120K Reputation points
    2023-07-16T17:04:35.3866667+00:00

    To take into consideration the special cells, try this fragment:

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim formula_cnt As Long
    formula_cnt = 0
    
    Dim c As Range
    For Each c In ws.UsedRange.Cells
        If Len(c.FormulaArray) > 0 Then formula_cnt = formula_cnt + 1
    Next
    
    MsgBox formula_cnt
    
    0 comments No comments

  2. Tanay Prasad 2,145 Reputation points
    2023-07-17T05:55:51.8+00:00

    Hi,

    Try this code-

    Sub CountFormulas()
        Dim rng As Range, formula_cnt As Long
        Dim cell As Range
        
        formula_cnt = 0
        
        For Each rng In Selection
            For Each cell In rng
                If cell.HasFormula Then
                    formula_cnt = formula_cnt + 1
                End If
            Next cell
        Next rng
        
        MsgBox formula_cnt
    End Sub
    
    
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.