Hi, Kenneth, thank you very mutch!
i am working in the button between blue colors, macro name "Sub LastRowLoopAREAS_IF3()".
your code is great, but if the column is entire empty, returns error!
beacuse this, i want other solution to count if exists one or more data of each type (numbers, formulas, text, etc) in the column before set the range.
Example:
Sub MainKennethXman1()
Dim r As Range, rr As Range
Set r = Range("A:A")
If IsEmpty(r) Then
Exit Sub
Else
Set r = Range("A:A").SpecialCells(xlCellTypeFormulas, xlNumbers)
Set rr = Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
but need determine the type of data (number, text, etc) to not error occurs in the start of macro.
I'm trying this apt this:
in this link i found the below code "https://exceljet.net/formula/count-cells-that-contain-text"
=SUMPRODUCT(--ISTEXT(rng))
You can also use SUMPRODUCT to count text values along with the function ISTEXT like so:=SUMPRODUCT(--ISTEXT(rng))
The double hyphen (called a double unary) coerces the result of ISTEXT from a logical value of TRUE or FALSE, to 1's and 0's. SUMPRODUCT then sums these values together to get a result.
And use this.
If Application.WorksheetFunction.IsText(Range("A1")) Then MsgBox "IsText"
If Application.WorksheetFunction.IsNumber(Range("A1")) Then MsgBox "IsNumber"
If IsNumeric(Range("A1")) Then MsgBox "IsNumeric"
Example:
Function test(var1 As Range, var2 As Range, rng1 As Range, rng2 As Range)
Dim strFormula As String
strFormula = "SumProduct((" & rng1.Address & "=" & var1.Address & ") * 1,(" & rng2.Address & "<" & var2.Address & ") * 1)"
test = Application.Evaluate(strFormula)
End Function
I know workarounds but i need know this way with Evaluate and Sumproduct, CountIf or Application.WorksheetFunction because are more powerful and versatile and fast.
thanks.