Iterating through a range of cells with a message at the end

I have data in two columns - column A and column B. Values in column b can be either 0, 5 or 6. I want to iterate through all the cells in column B. If the value of a particular cell =0, then I want to add the value from the cell to the left of it to an array; if it is <>0, then I just want to go on to the next cell. Once I have finished the iteration, I want a Message Box to appear depending on if the array is empty or not. In the array = 0, then the message should be "No missing data"; if the array has data, then "Missing data for: " + the values stored within the array.

I've never really worked with arrays in VBA so I'm not too sure how to proceed.

This is how I've structured the code so far:

Sub Checker()
   
    
    Dim dataRange As Range
    Dim cell As Range
   
   

  
    ' Define the range starting from B2 to the last cell with data in column B
    Set dataRange = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)

    ' Iterate through the range
    For Each cell In dataRange
    
        If cell.Value = 0 Then
            ' If the cell value is 0, add the value of the cell to the left to the array
            :confused:
        End If
        
    Next cell

    ' Check the array
    If :confused: = 0 Then
        ' If the array is empty, show a message box
        MsgBox "No data is missing"
    Else
       :confused:
    End If
End Sub

Any help with the array bits of the code would be most appreciated. I've put in the bits where I'm unsure how to proceed.

In the range being iterated there will never be a blank cell. It will always have either 0, 5 or 6.

I've amended the For loop to the following. However, when it does encounter cell = 0 it throws an Out of Range Error on the ReDim line, which I'm not quite sure why.

Sub Mising_Store_Checker()

    Dim dataRange As Range
    Dim cell As Range
    Dim dataArray() As Variant
 

    ' Define the range starting from B2 to the last cell with data in column B
    Set dataRange = Range("B2:B" & Cells(Rows.Count, "B").End(xlUp).Row)
 For Each cell In dataRange
    
        If cell = 0 Then
        
        
            ' If the cell value is 0, add the value of the cell to the left to the array

            ReDim Preserve dataArray(0 To UBound(dataArray) + 1)
            dataArray(UBound(dataArray)) = cell.Offset(0, -1).Value

        End If
        
    Next cell

Apologies for this. I will take better care with the rules going forward.

As for the cross-post, the forum is not allowing me to link directly as I still have too few posts, so I'll have to post it this way: vbaexpress. com/forum/showthread.php?71370-Iterating-through-a-range-of-cells-with-a-message-at-the-end

I have, in the meantime, been able to sort out the query in my own way.

Originally Posted by AliGW

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.

Please see about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important:

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

Originally Posted by sintek

Well...Tx for letting us know...sadly time wasted offering you solutions which you will not be making use of...

Apologies again. Not that it's much consolation for you (and I'm not meaning to add insult to injury), but I did learn quite a bit from studying all the solutions offered here.