Stop Macro if Condition Met

Would this work?

Sub Try()
    Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If c.Value = "N/A" Then MsgBox "Found it in cell " & c.Address: Exit Sub
    Next c
End Sub

jolivanes' Code could also work like this;

Option Explicit
Sub Try()
    Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If c.Text = "#N/A" Then MsgBox "Found it in cell " & c.Address
    Next c
End Sub

Hi, prabhubox@yahoo.com,

is it really #NA thatīs in the area?

Sub Stopmacro1()
With ActiveSheet
  If WorksheetFunction.CountIf(.Range("A2", .Range("A" & Rows.Count).End(xlUp)), "#NA") > 0 Then
    MsgBox ("Please check the alainment")
    Exit Sub
  End If
End With
End Sub

Ciao,
Holger

This code should work,

Option Explicit
Sub Stopmacro()
Dim LastRow As Long

LastRow = Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & LastRow).Text = "#N/A" Then
   MsgBox ("Please check the alainment")
   Exit Sub
  End If
 End Sub

Regards.

@ HaHoBe,

Hello Holger,

Maybe with your code like this?

Sub Stopmacro1()
With ActiveSheet
  If WorksheetFunction.CountIf(.Range("A2", .Range("A" & Rows.Count).End(xlUp)), "#N/A") > 0 Then
    MsgBox ("Please check the alainment")
    Exit Sub
  End If
End With
End Sub

You always seem to come up with much better Code, and I think we should ban you from this Forum, just for that.LOL

Kind Regards my Friend!

Hi, Winon,

we would need to know if itīs an error returned from a function or a hard-coded value we are searching - code is okay for hard-coded values but Iīm afraid will not work on formulas where you would need to use a loop (according to the tiny bit of information provided: the code only).

Ciao,
Holger

Hi Holger,

Thanks for the feedback.

You really are on your toes today, and very alert!

It is a very valid point you are making, but I am pretty sure it relates to a Function error, and not hard coding.

I guess we will have to wait for more clarification on this.


Ciao

Hi, prabhubox,

if itīs a formula/function you may try the following code

Sub Stopmacro2()
Dim lngWrong As Long
On Error Resume Next
With ActiveSheet
  lngWrong = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas, 16).Cells.Count
End With
On Error GoTo 0
If lngWrong > 0 Then
  MsgBox ("Please check the alainment")
  Exit Sub
End If
MsgBox "here we go"
End Sub

Ciao,
Holger

@ HaHoBe,

Hello Holger,

Wow!, What can I say?, but BRILLIANT!

I "Hate" you

Well done Friend!

Kind regards.

Hello prabhubox,


Would you mind telling us which of the many solutions offered to you, solved your issue?

It would also be good to know if you had an issue with #NA or #N/A.

Thank you.