用字符串值替换空白/空单元格

Hope someone can help. I need to populate any blank/empty cells within a range of cells with a specific string. I also don't know that the last row would be in that range so I am looking for that first, I know that line of code works as I have used it for another function within the script. Below is the code I am using: -

希望有人可以帮助你。我需要用一个特定的字符串填充所有单元格内的空/空单元。我也不知道最后一行会在那个范围内，所以我在寻找第一个，我知道这行代码的工作，就像我在脚本中使用它的另一个函数一样。下面是我正在使用的代码:-

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row

    For Each r In Range("AS22:AU" & LastRow)
        If r.Value2 = vbNullString Then
            r.Value2 = "Greens"
        End If
    Next

I don't seem to get any compile errors when I run the code, it just does not work, I still have the same blank/empty cells.

当我运行代码时，我似乎没有得到任何编译错误，它只是不工作，我仍然有相同的空白/空单元格。

Can anyone shed any light onto what I am doing wrong?

有人知道我做错了什么吗?

3 个解决方案

#1

You could just use the .Replace Method with the range. It would be much quicker.

您可以使用. replace方法来表示范围。那样会快得多。

Range("AS22:AU" & LastRow).Replace "", "Greens", xlWhole

#2

How about:

如何:

Sub dural()
    Dim r As Range, LastRow As Long

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row
    For Each r In Range("AS22:AU" & LastRow)
        If r.Text = "" Then r.Value = "Greens"
    Next r
End Sub

This will change:

这将改变:

truly empty cells
真正的空细胞
cells containing formulas returning Null
包含返回Null的公式的单元格
cells containing Null character as a constant
包含Null字符的单元格。

#3

Use IsEmpty() to test for blanks.

使用IsEmpty()测试空格。

    If IsEmpty(r.Value2) Or Trim(r.Value2) = "" Then
        r.Value2 = "Greens"
    End If

Also watch out for cells with a zero length string or a space. They appear empty but in fact are not.

同样要注意有零长度字符串或空格的单元格。它们看起来是空的，但实际上不是。

#1