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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
yea but where do I put the rest of my code to continue on? I have this following it and I am not sure where to put it so I do not get an error

Call SORT
'''This checks to make sure there is a date and day placed in the 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"

Else

If Not Sheets("Drop In Sheet").Range("B3").Value = "Monday" And Sheets("Data-History").Range("D5").Value = "" Then
MsgBox ("The Data-History spreadsheet must be started with Monday")

Else

If Sheets("Drop In Sheet").Range("L3").Value = "" Then
MsgBox ("Please insert data ( the first available row cannot be blank )")

Else
''''''''''''''''The following is used for one row of data''''''''''''''
'Delete the blank rows in the table'''
Dim Rng As Range
On Error Resume Next
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226

ADVERTISEMENT

and I am not sure what changing that does. I changed it and the msgbox still appears for instance that the case is met instead of just once.
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
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"
End If
Next
End Sub


"other code"<<<<<<<<<<<<<<<< where do I put this? Is the below example correct?


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"
Else

"Other Code"
End If
Next
End Sub
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226

ADVERTISEMENT

I tried placing the other code after the next and it doesn't seem to work.
 

kevatarvind

Well-known Member
Joined
Mar 3, 2013
Messages
1,047
yes you can put other code there it will work or facing any problem then let me know your full code what you want to put there ?
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
if I have invalid name listed twice the msgbox appears as many times as the invalid name appears which would be twice. How can I get it to only appear once?
 

Eraengineer

Board Regular
Joined
Jun 12, 2011
Messages
226
Just do this..... place 1's in cells A1 to A5 and then run the macro and you will see that the pop up box appears 5 times... Aslo if you have all cells through the range A1:A10 then you will see the msgbox"other code" appear 10 times. I need both these to only occur once.

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"
Else

MsgBox"Other Code"
End If
Next
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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