I work a few hours with the property HasFormula and that always generates me an error message. But fortunately, I just found the solution to my problem.
Indeed, The HasFormula property (which is read-only) returns True if the single cell range contains a formula. It returns False if the cell does not have a formula.
If the range consists of more than one cell, VBA returns True only if all cells in the range contain a formula, or False if all cells in the range don’t have a formula. The property returns a Null if there is a mixture of formulas and nonformulas in the range. Null is kind of a no-man’s land: The range contains
a mixture of formulas and values.
You need to be careful when you work with properties that can return Null. More specifically, the only data type that can deal with Null is Variant.
And so that's the mistake I made.
For example, assume that cell A1 contains a value and cell A2 contains a for-
mula. The following statements generate an error because the range doesn’t
consist of all formulas or all nonformulas:
Dim FormulaTest As Boolean
FormulaTest = Range(“A1:A2”).HasFormula
The Boolean data type can handle only True or False. Null causes it to complain and throw up an error message. To fix this type of situation, the best thing to do is make sure that the FormulaTest variable is declared as a Variant rather than as a Boolean. The following example uses VBA’s handy TypeName function (along with an If-Then statement) to determine the data type of the FormulaTest variable. If the range has a mixture of formulas and nonformulas, the message box displays Mixed!
Dim FormulaTest As Variant
FormulaTest = Range(“A1:A2”).HasFormula
If TypeName(FormulaTest) = “Null” Then MsgBox “Mixed!”
