VBA 检查列中的空单元格并打印值

VBA check for empty cells in column and print value

提问by DL1

In column J there are empty rows and rows with a value such as checked.

在 J 列中，有空行和带有值（例如已选中）的行。

I have tried to write VBA code that prints "unchecked" where there is an empty cell AND this works, but when it hits a cell with a value (checked) it stops. And it won't go down to the next cell probably because I have formulas in the cell that prints nothing if not fullfilled, but it still contains that formula. In my case I have empty cells until J7 and then it starts again at J15. But this can change from time to time regarding source data.

我曾尝试编写 VBA 代码，在有空单元格的地方打印“未选中”，并且这有效，但是当它遇到带有值（已选中）的单元格时，它会停止。它不会转到下一个单元格，可能是因为我在单元格中有公式，如果未满则不打印任何内容，但它仍然包含该公式。就我而言，我在 J7 之前有空单元格，然后在 J15 处再次开始。但是对于源数据，这可能会不时发生变化。

The reason I want to do it like this is because I have a formula in column J that already have printed some values and then some VBA code that checks for other values in a different column and prints to column J. Column J is the filter master column sort of. So this is the way I have to do it I guess.

我想这样做的原因是因为我在 J 列中有一个公式，它已经打印了一些值，然后是一些 VBA 代码，用于检查不同列中的其他值并打印到 J 列。 J 列是过滤器主列排序。所以我猜这就是我必须这样做的方式。

My code right now is,

我现在的代码是，

Sub DoIfNotEmpty()
    Dim ra As Range, re As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Set ra = .Range("J:j25")
        For Each re In ra
            If IsEmpty(re.Value) Then
                re.Value = "unchecked"
            End If
        Next re
    End With
End Sub 

Can I print to empty cells if the cell contains a formula which in this case has an ifstatement that is not filled?

如果单元格包含一个公式，在这种情况下有一个if未填充的语句，我可以打印到空单元格吗？

回答by P??

Except from @Maxime Porté's points out that it should be .Range("J1:j25"). I guess the cells only look empty, but they are not.

除了@Maxime Porté 指出它应该是.Range("J1:j25"). 我猜这些单元格看起来只是空的，但实际上不是。

A cell that contains an empty string, "", is not empty anymore, but it looks like it. You can test it like this:

包含空字符串 , 的单元格""不再是空的，但看起来像它。你可以这样测试：

1. In a new worksheet write in A1: =""(there is no space in between!)
2. Copy A1 and special paste values in A1. A1 now looks to be empty.
3. Run Debug.Print IsEmpty(Range("A1").Value)in VBA and you get a FALSE.

1. 在新的工作表中写入 A1 ：（=""中间没有空格！）
2. 复制 A1 和 A1 中的特殊粘贴值。A1 现在看起来是空的。
3. Debug.Print IsEmpty(Range("A1").Value)在 VBA 中运行，你会得到一个FALSE.

The cell A1 is not empty any more, because it contains an empty string.

单元格 A1 不再为空，因为它包含一个空字符串。

What can you do?

你能做什么？

Sub DoIfNotEmpty()
    Dim ra As Range, re As Range

    With ThisWorkbook.Worksheets("Sheet1")
    Set ra = .Range("J1:J25")
        For Each re In ra
            If IsEmpty(re.Value) or re.Value = vbNullString Then
                re.Value = "unchecked"
            End If
        Next re
    End With
End Sub

This will mark pseudo empty cells as "unchecked" too. But be aware that it also kills formulas that result in an empty string, "".

这也会将伪空单元格标记为“未选中”。但请注意，它也会杀死导致空字符串的公式，"".

回答by user3598756

You could exploit the Specialcells()method of Rangeobject:

您可以利用object的Specialcells()方法Range：

Sub DoIfNotEmpty()
    ThisWorkbook.Worksheets("Sheet1").Range("J1:J25").SpecialCells(xlCellTypeBlanks).Value = "unchecked"
End Sub

Or, if you have formulas returning blanks, then AutoFilter()"blank" cells and write in them

或者，如果您有返回空白的公式，则AutoFilter()“空白”单元格并写入其中

Sub DoIfNotEmpty()
    With ThisWorkbook.Worksheets("Sheeet1").Range("J1:J25") '<--| reference your range (first row must be a "header")
        .AutoFilter Field:=1, Criteria1:="" '<--| filter its empty cells
        If Application.WorksheetFunction.Subtotal(103, .cells) > 1 Then .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Value = "unchecked" '<--| if any cell filtered other than headers then write "unchecked" in them
        .Parent.AutoFilterMode = False
    End With
End Sub