EXCEL VBA - Miscalculation

Question

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:

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

Thank you in advance.

Sincerely,

TheWorldOfAlgorithms

Answer 1

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

Answer 2

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