PDA

View Full Version : Range Values



Bishma1982
06-27-2016, 08:08 AM
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
06-27-2016, 10:41 AM
change

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

Paul_Hossler
06-27-2016, 01:01 PM
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
06-27-2016, 06:18 PM
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
06-27-2016, 07:15 PM
This helped a lot Offthelip! Thanks..

Bishma1982
06-27-2016, 07:17 PM
change

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


This helped a lot. Thanks

Bishma1982
06-27-2016, 07:19 PM
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
06-27-2016, 07:20 PM
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