CODEWITHSUNDEEP
excelvbaexcel-formula
wazami
wazami

Reputation: 23

How to detect if a cell with a formula in it is blank?

I am trying to write a macro that looks at a column and if a cell is not blank adds all the values together and also counts how many cells were added together.

The problem is all the cells in the column are referencing a different cell on a separate sheet. Therefore, all the cells technically have something in them (e.g. something like =Detail!E5).

The cells to count have a number between 0 and 100. Whilst the "blank" cells have a formula referencing the original cell, and that formula is returning " " (A space).

Does anybody know how this can be achieved?

I have tried a couple of things but they always just return a count of all the cells instead of the populated ones.

Set myRange = Range("J13")


For iCol = 0 To 18
        If myRange.Offset(0, iCol).Value > result Then
            For iRow = 17 To 31
                If myRange.Offset(iRow, iCol).Value <> " " Then
                    counter = counter + 1
                    Debug.Print (counter)
                End If
            Next iRow
        End If
Next iCol

Upvotes: 1

Views: 302

Answers (2)

VBasic2008
VBasic2008

Reputation: 54757

Count and Sum Values (Loop)

It is unclear what the result is. Adjust in the constants section.

The Code

Sub CountVals()

    Const cSheet As String = "Sheet1"
    Const cRange As String = "J13"
    Const cCols As Long = 19
    Const cFirstR As Long = 30
    Const cLastR As Long = 44
    Const result As Long = 21 ' Long, Single, Double ?

    Dim myRange As Range
    Dim FirstC As Long
    Dim LastC As Long
    Dim counter As Long
    Dim colCounter As Long
    Dim summer As Long
    Dim colSummer As Long
    Dim i As Long
    Dim j As Long


    With ThisWorkbook.Worksheets(cSheet)
        Set myRange = .Range(cRange)
        FirstC = myRange.Column
        LastC = FirstC + cCols - 1
        For j = FirstC To LastC
            Set myRange = .Cells(myRange.Row, j)
            If myRange.Value > result Then
                For i = cFirstR To cLastR
                    If IsNumeric(.Cells(i, j).Value) Then
                        summer = summer + .Cells(i, j).Value
                        colSummer = colSummer + .Cells(i, j).Value
                        counter = counter + 1
                        colCounter = colCounter + 1
                    End If
                Next
                Debug.Print "Column" & j & " = " & colSummer & "(" _
                        & summer & ") - " & colCounter & "(" _
                        & counter & ")" ' for each column
                colCounter = 0
                colSummer = 0
            End If
        Next
    End With

End Sub

Upvotes: 0

user10981853
user10981853

Reputation:

SpecialCells can determine whether text or a number has been returned from a formula but you might be better off with simple worksheet functions.

dim n as long, t as long
Set myRange = Range("J13")

For iCol = 0 To 18
    If myRange.Offset(0, iCol).Value > result Then
        with myRange.Offset(17, iCol).resize(15, 1)

            'count numbers returned from formulas
            n = application.count(.cells)
            'count text returned from formulas
            t = application.counta(.cells) - application.count(.cells)

            debug.print n & "numbers"
            debug.print t & "texts"

            on error resume next
            'count numbers returned from formulas
            n = 0
            n = .specialcells(xlCellTypeFormulas, xlNumbers).count
            'count text returned from formulas
            t = 0
            t = .specialcells(xlCellTypeFormulas, xlTextValues).count
            on error goto 0

            debug.print n & "numbers"
            debug.print t & "texts"

        end with
    End If
Next iCol

Upvotes: 2

Related Questions