Load a string or array using vba by looping through multiple Sheets

Cross posted at :
https://www.mrexcel.com/board/thread...heets.1171919/

Reason :
No solution for some time now.

Hello guys,
I have tried to narrow down what I want to achieve to this level and I think with a little push from the seniors on this great platform, my challenge will be handled.

Sub GetMissingNum ()
    Dim e&, lr&, sht As Worksheet 
        For Each sht In Worksheets 
             Select Case sht.Name 
                 Case "Data 1", "Data 2", "Data 3", "Report 1", "Report 2"
                  lr = sht.Cells (Rows.Count, "A").End (xlUp).Row
                  If lr > 3 then
                  e  = 0
                  With sht.Range("A4:A" & lr)
                       Do 
                           e = e + 1
                       Loop Until IsError(Application.Match(e, .Cells, 0))
                       ' I want to load e into a string or array like:
                       ' ray = ("..., ..., ....") or 
                       ' ray = Array(..., ...., .....)
                  End With 
                  End If
            End Select 
         Next sht 
End Sub

I want someone to help me out with how to load each of the e into the string or array as explained above?

Thanks in advance.

From the workbook, I have 5 sheets which reflect the sheet names used in my code above.

On data 1, 2 is the first missing number from the series
On data 2, 6 is the first missing number from the series
On data 3, 1 is the first missing number from the series
On report 1, 8 is the first missing number from the series
On report 2, 7 is the first missing number from the series

So I want to load those numbers to a string variable or an array variable as below.

ray = Array(2, 6, 1, 8, 7) or
ray = ("2, 6, 1, 8, 7")

Thanks to you geniuses @Sintek and @Jindon

Kelly mort,
Try

Sub test()
    Dim ws As Worksheet, LR As Long, n As Long, x, myList
    ReDim myList(1 To Worksheets.Count)
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Data 1", "Data 2", "Data 3", "Report 1", "Report 2"
                LR = ws.Range("a" & Rows.Count).End(xlUp).Row
                If LR > 3 Then
                    x = ws.Evaluate("min(if(isna(match(row(1:" & LR - 2 & "),a4:a" & LR & ",0)),row(1:" & LR - 2 & ")))")
                    If x > 0 Then n = n + 1: myList(n) = x
                End If
        End Select
    Next
    If n > 0 Then ReDim Preserve myList(1 To n)
    MsgBox IIf(n > 0, Join(myList, ", "), "No missing data")
End Sub

Just for your info,

My code will work with all versions of Excel in terms of generating 1D array.

Originally Posted by jindon

Just for your info,

My code will work with all versions of Excel in terms of generating 1D array.

Hello

In case I want to return the smallest value from the list of numbers that I have generated from your code above, how do I get it with ease?

Thanks again.

Do you mean Minimum out of the result?

Sub test()
    Dim ws As Worksheet, LR As Long, n As Long, x, myList
    ReDim myList(1 To Worksheets.Count)
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Data 1", "Data 2", "Data 3", "Report 1", "Report 2"
                LR = ws.Range("a" & Rows.Count).End(xlUp).Row
                If LR > 3 Then
                    x = ws.Evaluate("min(if(isna(match(row(1:" & LR - 2 & "),a4:a" & LR & ",0)),row(1:" & LR - 2 & ")))")
                    If x > 0 Then n = n + 1: myList(n) = x
                End If
        End Select
    Next
    If n > 0 Then
        ReDim Preserve myList(1 To n)
        MsgBox "Minimum = " & Application.Min(myList) & vbLf & "out of " & Join(myList, ", ")
    Else
        MsgBox "No data available"
    End If
End Sub

Originally Posted by jindon

Do you mean Minimum out of the result?

Sub test()
    Dim ws As Worksheet, LR As Long, n As Long, x, myList
    ReDim myList(1 To Worksheets.Count)
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Data 1", "Data 2", "Data 3", "Report 1", "Report 2"
                LR = ws.Range("a" & Rows.Count).End(xlUp).Row
                If LR > 3 Then
                    x = ws.Evaluate("min(if(isna(match(row(1:" & LR - 2 & "),a4:a" & LR & ",0)),row(1:" & LR - 2 & ")))")
                    If x > 0 Then n = n + 1: myList(n) = x
                End If
        End Select
    Next
    If n > 0 Then
        ReDim Preserve myList(1 To n)
        MsgBox "Minimum = " & Application.Min(myList) & vbLf & "out of " & Join(myList, ", ")
    Else
        MsgBox "No data available"
    End If
End Sub

You are a legend! !!!!

I am learning a lot. Thanks again.

Can you please explain this line for me?

x = ws.Evaluate("min(if(isna(match(row(1:" & LR - 2 & "),a4:a" & LR & ",0)),row(1:" & LR - 2 & ")))")

That line extracts minimum number that deviated from sequential numbers starting from 1.

row(1:" & LR - 2 & ") creates array of {1,2,3,4,5,6, up to LR-2}
-2 is just for adjustment of starting row that is 4(A4) and it is not necessary in this case.

isna(match(row(1:" & LR - 2 & "),a4:a" & LR & ",0))
find if any of the number in array is/are missing in A4:A LR

You can actually view how it works on the sheet.
Select D4:D10 in "Data 1" sheet and enter formula
=ISNA(MATCH({1;2;3;4;5;6;7;8;9;10;11},A4:A10,0))
and confirm with Ctrl + Shift + Enter (array formula entry)

Now you see
FALSE
TRUE
FALSE
FALSE
FALSE
TRUE
FALSE

So, the minimum missing number is 2nd row then Min returns 2 from 2nd row(1:" & LR - 2 & ").

HTH

Okay thanks for the explanation.

I just observed something that I want you to look at it for me so that I don't enter into some serious trouble later:

I am running some test with Data 1 and Data 2 sheets.

Numbers on Data 1 are in this order:

3
2
1
10
7
4

And when I run the code with all other sheets being blank, it returns 5 which is correct.

Then I added some data to Data 2 sheet as below:

1
2
3
4
5

This time, the code gives me 5 instead of 6.

It should give you 6 (tested here).

If not on your side, if you upload a workbook that is giving 5, I will look at it.

With the data that I provided, the code should give 5 as the min value and your code is correct. The issue is from my end - the logic that I gave (just finding the smallest value) is not enough!

Data1 has this:
3
2
1
10
7
4

And this part will return 5 as the min value.

Then Data 2 has this:
1
2
3
4
5

And this part will return 6 as the min value.

Now we have the list as (5,6).
So when we run the min code against the list, we surely return 5.

The actual number that I want to return (defined previously as min value from the list of missing numbers), is a number that should not be present on any of the sheets.

I never anticipated this kind of trap when I was thinking it through. I was thinking getting the missing numbers then getting the smallest out of the list could save me.

But I just realized that the smallest from the list could also be found on other sheets.

So the goal in a simpler form, is to locate a missing number, that does not exist on any of the sheets.

Can that be done?

Then it should return 6 for those 2 data sets?

OK, now I understand what you are trying to do.
I need to go out now, so I will post a code later like 3-4 hours.

OK. I will be waiting then.

See if this is how you wanted.

Sub test()
    Dim ws As Worksheet, LR As Long, n As Long, x, myList, a, e
    ReDim myList(1 To Worksheets.Count)
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Data 1", "Data 2", "Data 3", "Report 1", "Report 2"
                LR = ws.Range("a" & Rows.Count).End(xlUp).Row
                If LR > 3 Then
                    x = Application.Max(ws.Range("a4:a" & LR)) + 1
                    If IsEmpty(a) Then
                        ReDim a(1 To x) As Boolean
                    Else
                        If x > UBound(a) Then ReDim Preserve a(1 To x)
                    End If
                    x = ws.Evaluate("min(if(isna(match(row(1:" & LR & "),a4:a" & LR & ",0)),row(1:" & LR & ")))")
                    n = n + 1: myList(n) = x
                    For Each e In ws.Range("a4:a" & LR).Value
                        a(e) = True
                    Next
                End If
        End Select
    Next
    If n > 0 Then
        ReDim Preserve myList(1 To n)
        x = Application.Match(False, a, 0)
        MsgBox "Minimum = " & x & vbLf & "out of " & Join(myList, ", ")
    Else
        MsgBox "No data available"
    End If
End Sub

Originally Posted by jindon

See if this is how you wanted.

Sub test()
    Dim ws As Worksheet, LR As Long, n As Long, x, myList, a, e
    ReDim myList(1 To Worksheets.Count)
    For Each ws In Worksheets
        Select Case ws.Name
            Case "Data 1", "Data 2", "Data 3", "Report 1", "Report 2"
                LR = ws.Range("a" & Rows.Count).End(xlUp).Row
                If LR > 3 Then
                    x = Application.Max(ws.Range("a4:a" & LR)) + 1
                    If IsEmpty(a) Then
                        ReDim a(1 To x) As Boolean
                    Else
                        If x > UBound(a) Then ReDim Preserve a(1 To x)
                    End If
                    x = ws.Evaluate("min(if(isna(match(row(1:" & LR & "),a4:a" & LR & ",0)),row(1:" & LR & ")))")
                    n = n + 1: myList(n) = x
                    For Each e In ws.Range("a4:a" & LR).Value
                        a(e) = True
                    Next
                End If
        End Select
    Next
    If n > 0 Then
        ReDim Preserve myList(1 To n)
        x = Application.Match(False, a, 0)
        MsgBox "Minimum = " & x & vbLf & "out of " & Join(myList, ", ")
    Else
        MsgBox "No data available"
    End If
End Sub

Hello Jindon,

Sorry for waking up this thread again but I need one more help.

In case I want to point to column M on the report 2 sheet, how do I adjust the code to do that for me?

That is for all the other sheets, I am pointing to column A for the numbers but when I get to report 2, I want to look at column M for the numbers.

The code is doing well and producing the right output.
But when one or more sheets have only one row filled, say Data 1 has only one record, the code runs into a mismatch error on this line:

For Each e In ws.Range("a4:a" & LR).Value

Change

                    For Each e In ws.Range("a4:a" & LR).Value
                        a(e) = True
                    Next

to

                    For Each e In ws.Range("a4:a" & LR + 1).Value
                        If e <> "" Then a(e) = True
                    Next

Originally Posted by jindon

Change

                    For Each e In ws.Range("a4:a" & LR).Value
                        a(e) = True
                    Next

to

                    For Each e In ws.Range("a4:a" & LR + 1).Value
                        If e <> "" Then a(e) = True
                    Next

Problem solved.

Thanks again for your time.

Have a wonderful time.

@Jindon,
That's correct, from the question I asked above.

The real question should have been, pointing to both columns A and M on the Data * sheets.

So for data 1 to data 3, I want to point to both A and M but on the report sheets, I point to just column A.

I was a bit tired and could not concentrate properly while asking the question and I hope you could forgive me for that.

Then try

Sub test()
    Dim ws As Worksheet, LR As Long, n As Long, x, myList, a, e, s, cols
    ReDim myList(1 To Worksheets.Count * 2)
    For Each ws In Worksheets
        Select Case True
            Case ws.Name Like "Data *": cols = Array("a", "m")
            Case ws.Name Like "Report *": cols = Array("a")
            Case Else: cols = Empty
        End Select
        If IsArray(cols) Then
            For Each s In cols
                LR = ws.Range(s & Rows.Count).End(xlUp).Row
                If LR > 3 Then
                    x = Application.Max(ws.Range(s & "4:" & s & LR)) + 1
                    If IsEmpty(a) Then
                        ReDim a(1 To x) As Boolean
                    Else
                        If x > UBound(a) Then ReDim Preserve a(1 To x)
                    End If
                    x = ws.Evaluate("min(if(isna(match(row(1:" & LR & ")," & _
                    s & "4:" & s & LR & ",0)),row(1:" & LR & ")))")
                    n = n + 1: myList(n) = x
                    For Each e In ws.Range(s & "4:" & s & LR + 1).Value
                        If e <> "" Then a(e) = True
                    Next
                End If
            Next
        End If
    Next
    If n > 0 Then
        ReDim Preserve myList(1 To n)
        x = Application.Match(False, a, 0)
        MsgBox "Minimum = " & x & vbLf & "out of " & Join(myList, ", ")
    Else
        MsgBox "No data available"
    End If
End Sub

Thanks Jindon for it.

This is exactly what I needed.

Have a wonderful weekend.