Increment a variable while looping

  • Thread starter Thread starter ibeetb
  • Start date Start date
I

ibeetb

I have a procedure that loops through a range...I want this variable to
increment each time it comes upon an instance of something.
Ex:
For each cell in range
if cell.hasformula then
n="something"
end if
next cell

I want n to hold the value until it is finished looping and have a count of
the number of times cell.hasformula was true
 
You could loop through the formulas:

Option Explicit
Sub testme01()

Dim FormulaCtr As Long
Dim myCell As Range
FormulaCtr = 0
For Each myCell In Selection.Cells
If myCell.HasFormula Then
FormulaCtr = FormulaCtr + 1
End If
Next myCell

End Sub

Or just look at the cells that contain formulas:

Sub testme02()

Dim myRng As Range
Dim FormulaCtr As Long

On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
FormulaCtr = 0
Else
FormulaCtr = myRng.Cells.Count
End If

End Sub
 
Or even:

Sub testme02()
Dim FormulaCtr As Long
On Error Resume Next
FormulaCtr = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas)).count
On Error GoTo 0
End Sub
 
Back
Top