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

[Eraengineer]

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

 

[kevatarvind]

can you pls explain whta actually you want to do what is your full code ?

 

[Eraengineer]

I just need this..... [h=1]If a cell in range ="" then Message box(just once) else Call code[/h]

 

[kevatarvind]

try below

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

 

[Eraengineer]

close but if you have a blank cell in the range you dont get the message blahblah

 

[kevatarvind]

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

 

[Eraengineer]

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]

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]

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.

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]

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