if a cell in range = this then do that, else

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
Looking for vb that says if a cell in a range = "Invalid Name" then Msgbox("") Else continue with code. I tried a For each cell but I can't get it to continue with my code plus the For each wants to have a popup for every cell that does not meet the condition and all I want is the message box to appear once.

Sub checkfornames()
Dim r As Range, cell As Range
Set r = Range("Table1[Output]")
For Each cell In r
If cell.Value = "Invalid Name" Then
MsgBox ("blahblah")
End If
Next
End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
I just need this..... [h=1]If a cell in range ="" then Message box(just once) else Call code[/h]
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
try below
Code:
Sub checkfornames()
Dim r As Range, cell As Range
Set r = Range("A1:A10")
For Each cell In r
If cell.Value = "" Then
MsgBox "blahblah"
Exit Sub
Else
MsgBox "Other Code"
Exit Sub
End If
Next
End Sub
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047

ADVERTISEMENT

remove 2nd eXit Sub but in that case you will get other code msg each time
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
I want something like this. The only difference is that instead of selecting a range of 1 cell I want to check 10 cells.

If IsEmpty(Range("A3")) Then
MsgBox "Please fill in the date located in cell A3"

Else

If IsEmpty(Range("B3")) Then
MsgBox "Please fill in the day located in cell B3"
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I want something like this. The only difference is that instead of selecting a range of 1 cell I want to check 10 cells.

If IsEmpty(Range("A3")) Then
MsgBox "Please fill in the date located in cell A3"

Else

If IsEmpty(Range("B3")) Then
MsgBox "Please fill in the day located in cell B3"
Have you changed your conditions?
I thought you only wanted one Message Box, but it sounds like now you want one for each blank cell.
Also, since you are referencing the address of blank cell right in your Message Box, what would you show in there if there are multiple blank cells (and you only want one Message Box)?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,017
Office Version
  1. 365
Platform
  1. Windows
I took a hybrid approach, which I think should work for you. It goes through your entire range, collecting the addresses of all empty cells, and stores them in a single string.

It then returns a single Message Box listing all those blank cells if it finds any, and then exits the macro.
Code:
Sub checkfornames()
    
    Dim r As Range
    Dim myCellAddresses As String
    
    Application.ScreenUpdating = False
    
'   Loop through range, collecting cell addresses of empty cells
    For Each cell In Range("A1:A10")
        If IsEmpty(cell) Then
            myCellAddresses = myCellAddresses & cell.Address & ","
        End If
    Next cell
    
'   Check to see if any empty cells, and if so, return message box indicating empty cells
    If Len(myCellAddresses) > 0 Then
        MsgBox "Empty cells found in " & Left(myCellAddresses, Len(myCellAddresses) - 1), vbOKOnly, "FIX EMPTY CELLS!"
        Exit Sub
    Else
'   Call your macro here, paste the rest of your code here
        Call NextMacro
    End If

    Application.ScreenUpdating = True

End Sub
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
Wow, that is exactly what I was looking for. One message saying empty cells were found and need to be fixed. The issue I was having was with the search of cells throughout a given range. Thanks Joe4
 

Watch MrExcel Video

Forum statistics

Threads
1,130,132
Messages
5,640,310
Members
417,136
Latest member
reeton3

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