Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment a variable while looping
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment a variable while looping
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 ibeetb wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment a variable while looping
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 -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... 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 ibeetb wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping | Excel Discussion (Misc queries) | |||
Not Looping | Excel Discussion (Misc queries) | |||
Looping | Excel Discussion (Misc queries) | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |