Not Run Macro if different value is found

[Maria Clara]

Hi guys, I need a macro to check in a specific column if there is at least one single value <> than the one stored on variable Plant, and if it does the macro should not run.

I'm trying to use the code below but it does not check first for a <> value but to the same value and because of that the macro is not working.

Can anyone shed a light on this? Thanks a lot!

 Sub HistoricalYES()

Dim Plant As String

Dim rFound As Range

Plant = Sheets("RM").Range("E17").Value

  Sheets("ALL POs Report").Select
  Columns("C:C").Select
   
  Set rFound = Selection.Find(What:=Plant, LookAt:=xlWhole)
If Not rFound Is Nothing Then
  MsgBox "OK, please move to next question", vbInformation
  Else
  MsgBox "Attention! You report contains more than one Plant value, please correct!", vbCritical
End If

End Sub

 

[dan_l]

Dim avCheck As Variant
Set myrange = Range("B:B")
Dim flag As Integer

avCheck = myrange
For i = 1 To UBound(avCheck)
  If avCheck(i, 1) = plant Then
  flag = 1
  End If
Next i
   
If flag = 0 Then
  MsgBox "macro!"
End If
If flag = 1 Then
  MsgBox "No macro!"
End If

 

[GCExcel]

Hi Maria,
Same concept as proposed by dan_l but with the specifics of your initial code :

Sub HistoricalYES()

  Dim Plant As String
  Dim rg As Range
  Dim ar, i As Long
  Dim bDifferent As Boolean
 
 
  With Sheets("All POs Report")
      ar = .Range("C1:C" & .Range("C" & .Rows.Count).End(xlUp).Row).Value
  End With
  Plant = Sheets("RM").Range("E17")
 
  bDifferent = False
  For i = 1 To UBound(ar, 1)
      If ar(i, 1) <> Plant Then bDifferent = True: Exit For
  Next i

  If Not bDifferent Then
      MsgBox "OK, please move to next question", vbInformation
  Else
      MsgBox "Attention! You report contains more than one Plant value, please correct!", vbCritical
  End If

End Sub