Had to extend my previous sub. Need to find any of the three strings (errors, but stored as text). If found, msgbox with warning and stop the sub. If not found, call other sub.
Below is my code so far. The thing is that I get the other sub called also when the strings are found.
Sub Z_ZWR_sprawdzbledy()
Dim MyAr(1 To 3) As String
Dim ws As Worksheet
Dim aCell As Range, bCell As Range
Dim i As Long
Set ws = ThisWorkbook.Sheets("komunikat_OS_zwroty")
MyAr(1) = "#VALUE!"
MyAr(2) = "#N/A"
MyAr(3) = "#REF!"
With ws
'~~> Loop through the array
For i = LBound(MyAr) To UBound(MyAr)
Set aCell = Worksheets("komunikat_OS_zwroty").Cells.Find(What:=MyAr(i), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
MsgBox "UWAGA! Znaleziono bledy!" & vbNewLine & vbNewLine & "SPRAWDZ KOMORKI Z #N/A!, #N/D! lub #REF!"
Else
End If
Next
Call zwrot2
End With
End Sub
One possible solution. The code loops through the given range, and if there is an error, the message will appear and the variable "blnCheckErrors" is set to true.
After the loop is finished the if-statement will check the "blnCheckErrors". If it's false, then the given procedure/sub will be executed.
Sub Z_ZWR_sprawdzbledy()
Dim MyAr(1 To 3) As String
Dim ws As Worksheet
Dim aCell As Range, bCell As Range
Dim i As Long
Dim blnCheckErrors as boolean
Set ws = ThisWorkbook.Sheets("komunikat_OS_zwroty")
MyAr(1) = "#VALUE!"
MyAr(2) = "#N/A"
MyAr(3) = "#REF!"
With ws
'~~> Loop through the array
For i = LBound(MyAr) To UBound(MyAr)
'I optimizied the code here a little bit, because you did use the same statement as above (ThisWorkbook.Sheets("komunikat_OS_zwroty"))
Set aCell = .Cells.Find(What:=MyAr(i), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell 'Edit: What is this for? It seems that you don't use it again
MsgBox "UWAGA! Znaleziono bledy!" & vbNewLine & vbNewLine & "SPRAWDZ KOMORKI Z #N/A!, #N/D! lub #REF!"
blnCheckErrors = true
End If
Next
'the "Call"-statement isn't necessary anymore, just use the name of the sub/function
if blnCheckErrors = false then zwrot2
End With
End Sub