Range Values

[Bishma1982]

Can someone please help? I am trying to detect value in a range of cells from the user input and handle it accordingly by this piece of code.. But the it keeps going into the 'Else' part and pops up MsgBox "Sheet is ready to be submitted".. all the time!

Sub RangeDetectEntry()
Dim DataEntry As Range
Set DataEntry = Range("B1:B3")
If DataEntry Is Nothing Then
MsgBox "Enter value"
    Else
        MsgBox "Sheet is ready to be submitted"
    End If
End Sub

I modified the code just a bit but it keeps going into the Else part of the If loop ...

Sub RangeDetectEntry()
    Dim DataEntry As Range, cell As Range
    Set DataEntry = Range("B1:B3")
    
    For Each cell In DataEntry
     If cell Is Nothing Then
         MsgBox "Enter value"
    Else
         MsgBox "Sheet is ready to be submitted"
    End If
    Next cell
End Sub

[offthelip]

change

If cell Is Nothing Then
to
If cell.Value = "" Then

[Paul_Hossler]

Sub RangeDetectEntry()
Dim DataEntry As Range, cell As Range
Set DataEntry = Range("B1:B3")

For Each cell In DataEntry
If cell Is Nothing Then
MsgBox "Enter value"
Else
MsgBox "Sheet is ready to be submitted"
End If
Next cell
End Sub

Empty is not the samething as Nothing

DataEntry might have 3 cells with nothing in them, BUT the DataEntry range object has been Set to something, and is therefore not Nothing

[SamT]

If the code is not in a worksheet code module:

Sub RangeDetectEntry() 
    Dim DataEntry As Range 
    Set DataEntry = Sheets("Sheet1").Range("B1:B3") 'Adjust "Sheet1" as needed
    If DataEntry Is Nothing Then 
        MsgBox "Enter value" 
    Else 
        MsgBox "Sheet is ready to be submitted" 
    End If 
End Sub

[Bishma1982]

This helped a lot Offthelip! Thanks..

[Bishma1982]

change

If cell Is Nothing Then
to
If cell.Value = "" Then

This helped a lot. Thanks

[Bishma1982]

If the code is not in a worksheet code module:

Sub RangeDetectEntry() 
    Dim DataEntry As Range 
    Set DataEntry = Sheets("Sheet1").Range("B1:B3") 'Adjust "Sheet1" as needed
    If DataEntry Is Nothing Then 
        MsgBox "Enter value" 
    Else 
        MsgBox "Sheet is ready to be submitted" 
    End If 
End Sub

Yeah well my code was indeed in a module. I will give this a shot! Thanks

[Bishma1982]

Empty is not the samething as Nothing

DataEntry might have 3 cells with nothing in them, BUT the DataEntry range object has been Set to something, and is therefore not Nothing

Finally, I realised my mistake.. this is fantastic. Thanks