arraysexcelvbaloopsmsgbox

Search for multiple strings, msgbox when found, call other sub if not


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

Solution

  • 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