计算活动列上方最多两列的值（不包括标题） [英] Count cells with values in a column up to two above the active cell, excluding the header

问题描述

我正在尝试计算一列中所有具有高于活动单元格值（不包括标题）和活动单元格正上方的单元格的单元格。

I'm trying to count all the cells in a column that have values above the active cell excluding the header and excluding the cell immediately above the active cell.

例如，如果我有一列

1

5

4

N / AN / A

4

当前单元格

我希望当前单元格等于2。（计算5和4，不是N / AN / A，不是当前单元格上方的单元格，也不是第一个单元格。）

I want the current cell to equal 2. (Counting the 5 and 4, not the N/A N/A, not the cell above current cell, and not the first cell.)

该列中的单元格数量会有所不同。

The number of cells in the column will vary.

我希望连续260列具有此功能。

I want this for 260 consecutive columns.

推荐答案

尝试一下：

Sub counter()

Dim col As Integer
Dim lastrow As Integer
Dim cellcount As Integer

With ActiveCell
    col = .Column
    lastrow = .Row - 2
End With

cellcount = 0
For Each cell In ActiveSheet.Range(Cells(2, col), Cells(lastrow, col))

    If IsError(cell) Then GoTo skipcell

    If cell.Value > 0 And IsNumeric(cell) Then cellcount = cellcount + 1

skipcell:

Next cell

ActiveCell = cellcount

End Sub

它需要活动单元格，并找到选定的列，并在活动单元格上方找到两个单元格

It takes the active cell and finds the selected colum and find the cell two above the active cell.

然后每次查找到大于 0的值时，循环遍历添加到计数器的范围。

It then loops though the range adding to a counter each time it finds a value higher than "0"

根据OP在检查中添加的注释中的要求，以确保单元格中的日期为数字，并且该单元格中没有错误（＃N / A）值

As requested by OP in comments added in checks to ensure date in the cell is Numeric and that there is not a error (#N/A) value in the cell

还要求它跨越同一行中的260列。为此，使用 for 循环：

Also requested is for this to span 260 columns in the same row. For this, use of a for loop is employed:

Sub counter()

Dim firstCol as Integer
dim lastCol as Integer

firstCol = 1 'You can change this value depending on your first column
             ' for example you might use ActiveCell.Column

lastCol = firstCol + 260

Dim col As Integer

Dim lastrow As Integer
lastRow = 6  ' Make this the actual last row of the data to include

Dim cellcount As Integer

for col = firstCol to lastCol

    cellcount = 0
    For Each cell In ActiveSheet.Range(Cells(2, col), Cells(lastrow, col))

        If IsError(cell) Then GoTo skipcell

        If cell.Value > 0 And IsNumeric(cell) Then cellcount = cellcount + 1

    skipcell:

    Next cell

    ActiveSheet.Cells(lastRow + 2, col) = cellcount

Next col

End Sub

这篇关于计算活动列上方最多两列的值（不包括标题）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！