Test for empty cell

JJacob · Dec 27th 2004

Code

If activecell.Value > "" And activecell.Offset(0, 2).Value = "" Then
activecell.Offset(0, 3).Value = ("Check this line")
With activecell.Offset(0, 3).Font
  .Name = "Verdana"
        .Size = 8
        .ColorIndex = 5
        .Bold = True

I am trying to find a less volatile way of testing for missing data. This works perfectly if I delete cell contents in active cell before running this code but dosent work if that cell contained any data that was not deleted such as removed with a space bar.

Thanks for any help!!

yjoshi · Dec 27th 2004

Re: Test for empty cell

The problem is that, when you erase data with space bar, the value is not "" but is " " includes spaces. so good way is to trim the spaces and try again.

It worked for me... let me know if it works for you.

Code

Sub test()
    If ActiveCell.Value > "" And Trim(ActiveCell.Offset(0, 2).Value) = "" Then
    ActiveCell.Offset(0, 3).Value = ("Check this line")
    With ActiveCell.Offset(0, 3).Font
        .Name = "Verdana"
        .Size = 8
        .ColorIndex = 5
        .Bold = True
    End With
    End If
End Sub

Display More

JJacob · Dec 27th 2004

Re: Test for empty cell

Thanks for the help Yjoshi...it cleared it right up!! Hope yourself and family are OK with the quake / tsunamis disaster. Sounds horrific.

Anonymous · Dec 27th 2004

Re: Test for empty cell

If i remember correctly, I've occasionally had trouble using ="" to test for blank cells on files that have been imported. They sometimes come in with a hidden text-forcing entry that does not display and shows zero cell length. So, as an alternative (if you ever encounter problems, you might also try:

Code

Sub test() 
    If Len(Trim(ActiveCell.Value))=0 And Len(Trim(ActiveCell.Offset(0, 2).Value)) = 0 Then 
        ActiveCell.Offset(0, 3).Value = ("Check this line") 
        With ActiveCell.Offset(0, 3).Font 
            .Name = "Verdana" 
            .Size = 8 
            .ColorIndex = 5 
            .Bold = True 
        End With 
    End If 
End Sub

Display More

Test for empty cell

Participate now!

Share