Check if cell is empty and show error

r1d1

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello,
I couldnt find any similar question nad i also looked the problem up. I am new in the game :)

I have two sheets: One as a mask, so you can write the information in the cells ("mask") and one sheet as a table where the makro will copy the data in the mask an paste them in the table ("register table").

The Cell C2 in sheet "mask" is a cell with a dropdown list. I want my program to check if the cell is empty (nothing is selected in the dropdown lsit), and if so, to give an error message. Nothing worked so far.

Sub newEntry()
Application.ScreenUpdating = False
Sheets("register table").Select
Dim findword$, c, fA
Probenart$ = Sheets("mask").Range("C2")

If Sheets("mask").Range("C2") Is Nothing Then
MsgBox " C2 its empty!"
Exit Sub


With ActiveSheet.Cells
With .Columns(1)
Set c = .Find(findword$, LookIn:=xlValues)
'if c not null
If Not c Is Nothing Then
fA = c.Address
Do
Rows(c.Row + 1).Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> fA
Sheets("Eingabemaske").Select
Range("C3:C28").Select
Selection.Copy
Sheets("register table").Select
Rows(c.Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Selection.Interior.ColorIndex = 0
Sheets("mask").Select
Range("C2:C28").Select
Selection.ClearContents
Sheets("register table").Select
End If
End With
End With
End If

Application.ScreenUpdating = True
End Sub

Thanks!
- r1d1
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try
VBA Code:
If Sheets("mask").Range("C2") =""Then
       MsgBox " C2 its empty!"
       Exit Sub
End If
 
Solution

r1d1

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try
VBA Code:
If Sheets("mask").Range("C2") =""Then
       MsgBox " C2 its empty!"
       Exit Sub
End If
well that worked, lol

thank you very much!

but why it didnt work with Is Nothing or IsEmpty(Range("C2") ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
This should work
VBA Code:
If IsEmpty(Sheets("mask").Range("C2")) Then
But your original code won't as C2 is a range & there is not nothing
 

r1d1

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This should work
VBA Code:
If IsEmpty(Sheets("mask").Range("C2")) Then
But your original code won't as C2 is a range & there is not nothing
I understand, because of the dropdown lis, there is somethin.

Thank you
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Using Is Nothing tests to see if anything has been assigned to an object.
As Range("C2") is range object testing to see if it's nothing will always return False as It's a range. It has nothing to do with the DV.
 

r1d1

New Member
Joined
Dec 21, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Using Is Nothing tests to see if anything has been assigned to an object.
As Range("C2") is range object testing to see if it's nothing will always return False as It's a range. It has nothing to do with the DV.
okay now i understand
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,240
Messages
5,600,491
Members
414,383
Latest member
konmtu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top