在IF语句中测试为空 [英] Test for Null in IF statement

问题描述

我从下面得到了下面的Sub：根据公式值格式化单元格从JosieP

I got the Sub Below from here:Format cell based on formula value from "JosieP"

如果遇到一个但没有成功，我一直在尝试对Null单元格进行测试。如果遇到一个空单元格，我想为单元格添加一个颜色

I have been trying to test for Null cells as the sub fails if it encounters one but have had no success. If it encounters a null cell I want to add a color to the cell

If IsNull（rCell）Then rCell.Interior.Color = 8 不起作用但不会失败

如果clng（Left（right（rcell.value，2）） 1））< 3然后rcell.Interior.ColorIndex = 10 失败当有一个空单元格

if clng(Left(Right(rcell.value, 2), 1)) < 3 Then rcell.Interior.ColorIndex = 10 fails when there is a null cell

我尝试添加Not IsNull（rCell），所以我会有
``如果clng（Left（Right（rcell.value，2），1））< 3而不是IsNull（rCell）然后rcell.Interior.ColorIndex = 10`但是这个fais也是

I have tryed adding Not IsNull(rCell) so I would have ``if clng(Left(Right(rcell.value, 2), 1)) < 3 And Not IsNull(rCell) Then rcell.Interior.ColorIndex = 10` but this fais as well

谢谢

Sub Format()

Dim LastRow As Long
Dim WS As Worksheet
dim rCell as range

Set WS = Sheets("sheet1")

LastRow = WS.range("F" & WS.Rows.Count).End(xlUp).Row

for each rcell in WS.range("F2:F" & LastRow).cells

 If IsNull(rCell) Then rCell.Interior.Color = 8

if clng(Left(Right(rcell.value, 2), 1)) < 3 And Not IsNull(rCell) Then rcell.Interior.ColorIndex = 10

next rcell
End Sub

推荐答案

Excel中的单元格值不包含 Null 值。如果单元格为空，则为空。

Cell values in Excel never contain the Null value. If the cell is blank, it is Empty.

If IsEmpty(rCell.Value) Then ...

另外，空白的单元格与单元格不同包含一个空字符串（零长度字符串）。要测试那些，你使用

Also, a cell that is blank is different from a cell that contains an empty string (zero-length string). To test for those, you use

If Len(rCell.Value) > 0 Then ...

这篇关于在IF语句中测试为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！