Check if cell not empty

Question

I want to write a code that does "if cell(A) in row not empty then set text "Yes" in cell B in the same row, and this should loop through the whole sheet.

I do not have a code.

Sub Check()
    Dim N As Long, i As Long, j As Long
    N = Cells(Rows.Count, "A").End(xlUp).row
    j = 2
    For i = 2 To N
        If Cells(i, "A").Value = "??" Then
            Cells(j, "B").Value = "Yes"
            j = j + 1
        End If
    Next i
End Sub

but how do I make it check "if not empty" as the value?

possible duplicate of [Test for Null in IF statement](http://stackoverflow.com/questions/16944442/test-for-null-in-if-statement) — GSerg, Jun 08 '15 at 11:35

score 3 · Answer 1 · answered Jun 08 '15 at 16:09

3

Simply use "" to check for empty:

If .Cells(i, "A").Value <> "" Then ...

answered Jun 08 '15 at 16:09

Paul Ogilvie

25,048
4
23
41

R3uK · Answer 2 · 2015-06-09T08:33:04.693

1

You should use vbNullString which is the constant name to designate "" (empty string) value.

So it could be these two options :

If .Cells(i, "A").Value <> vbNullString Then

Or

If .Cells(i, "A").Value <> "" Then

Try this :

Dim IsRunning as Boolean
Sub Check()
    If IsRunning Then Exit Sub
    Dim N As Long, i As Long, j As Long
    IsRunning = True
    With ActiveSheet
        N = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 2 To N
            If .Cells(i, "A").Value <> vbNullString Then
                .Cells(i, "B").Value = "Yes"
            End If
        Next i
    End With
    IsRunning= False
End Sub

edited Jun 09 '15 at 08:33

answered Jun 08 '15 at 11:28

R3uK

14,417
7
43
77

`vbNullString` is not the same as `""` and is definitely not a constant name for it. The two might give same results in some comparisons due to implicit conversions VB performs, but [they are different](http://stackoverflow.com/a/20909528/11683). – GSerg Jun 08 '15 at 11:33
@GSerg : My bad if there are some cases it doesn't work, but `Const vbNullString = "" Member of VBA.Constants` and I don't really see how your link is related to this... – R3uK Jun 08 '15 at 11:40
1

This is (unfortunately) how the Object Browser displays it, making it confusing. The point is, `""` is the empty string (a valid place in memory that has a valid address but whose content has zero length) and `vbNullString` is the zero pointer of type string (no place in memory at all). The InputBox in the linked question returns `vbNullString` when the user cancels and `""` when the user submits an empty box. – GSerg Jun 08 '15 at 11:43
Thank you, I try to call it through a Private Sub Worksheet_Change(ByVal Target As Range) but it works 1 or 2 tmes then the file stops working. why`? – angular2neewbie Jun 08 '15 at 11:52
@GSerg : Ok thanks for the explanation, I only knew about the address difference. @jojou96 : Added a boolean to avoid multiple simultaneous executions and if it still fail, maybe get rid of the `ActiveSheet` reference and the `.` in front of `Cells(...` – R3uK Jun 08 '15 at 12:29
@jojou96 : Is your problem solved? I'm not sure your are familiar with the site, so to accept an answer, there is a tick (will turn green) under up/down vote. Click on it to mark your issue as solved! ;) And later, you can up/down vote everywhere you find useful/confusing! ;) – R3uK Jun 23 '15 at 07:08

Check if cell not empty

2 Answers2