This code will list all the formulas in the selection in the Immediate Window.
Sub ListFormulas()
Dim rCell As Range
If TypeName(Selection) = “Range” Then
For Each rCell In Selection.Cells
Debug.Print String(4, ” “) & rCell.Address(0, 0), rCell.Formula
Next rCell
End If
End Sub
Dim rCell As Range
If TypeName(Selection) = “Range” Then
For Each rCell In Selection.Cells
Debug.Print String(4, ” “) & rCell.Address(0, 0), rCell.Formula
Next rCell
End If
End Sub
If you have a range selected, it loops through each cell. Then it prints four spaces, the cell’s address ((0,0) means relative, e.g. A1 not $A$1), and the cell’s formula. Note that if a cell contains a value instead of a formula, the Formula property returns the value. I wonder why that is.
Why the four spaces? So I can paste into SuperUser and it thinks it’s code.
Why not avoid those cells that do not have formulas:
Dim rCell As Range
If TypeName(Selection) = “Range” Then
For Each rCell In Selection.Cells
If rCell.HasFormula Then
Debug.Print String(4, ” “) & rCell.Address(0, 0), rCell.Formula
End If
Next rCell
End If
End Sub
@Nick and Dick…
Here is another way to avoid cells that do not have formulas in them. This method has the advantage of not looping through, and testing, every cell in the selection as the SpecialCells method will only deliver a range of cells with formulas for the “For Each” statement to iterate through. Also note that I replaced the String function call with, in my opinion, the more apt Space function call.
Dim rCell As Range
If TypeName(Selection) = “Range” Then
For Each rCell In Selection.SpecialCells(xlCellTypeFormulas)
Debug.Print Space(4) & rCell.Address(0, 0), rCell.Formula
Next rCell
End If
End Sub
@Nick and Dick…
I guess I should have included an “On Error” statement just to protect against the Selection having no formulas in any of its cells…
Dim rCell As Range
On Error Resume Next
If TypeName(Selection) = “Range” Then
For Each rCell In Selection.SpecialCells(xlCellTypeFormulas)
Debug.Print Space(4) & rCell.Address(0, 0), rCell.Formula
Next rCell
End If
End Sub