How to load the outcome of a code into a string or array using vba by looping through multiple Worksheets

[kelly mort]

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.

 

[kelly mort]

Crap, I forgot about that line, Change that line with 'lr' to something like:

Dim eArray(1 To 5)     As Variant

It is changed later on to 'lr'

Another error message

Array already dimensioned

ReDim eArray(1 To lr) As Variant

 

[johnnyL]

Another error message

Array already dimensioned

ReDim eArray(1 To lr) As Variant

Double craps. Delete the ' As variant' from that line.

 

[kelly mort]

Double craps. Delete the ' As variant' from that line.

Like this?

ReDim eArray(1 To lr)

I get same error message

 

[johnnyL]

One minute ...

 

[johnnyL]

How about:

Sub GetMissingNum()
'
    Dim e&, lr&, eArraySlotNumber&, UsedCellsIneArray&, UsedeArraySlotNumber&, sht  As Worksheet
'
    Dim eArrayCellContents  As String
    Dim eArray()     As Variant
'
    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
'
                ReDim eArray(lr)
'
                If lr > 3 Then
                    e = 0
                    eArraySlotNumber = 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(..., ...., .....)
                        eArraySlotNumber = eArraySlotNumber + 1
                        eArray(eArraySlotNumber) = e
                    End With
                End If
        End Select
    Next sht
'
'
'   eArray check values section \/ \/ \/
'
    eArrayCellContents = ""
    UsedCellsIneArray = Application.Count(eArray) ' find # of used cells in an array
'
    If UsedCellsIneArray > 0 Then
        For UsedeArraySlotNumber = 1 To UsedCellsIneArray
            If UsedeArraySlotNumber < UsedCellsIneArray Then
                eArrayCellContents = eArrayCellContents & eArray(UsedeArraySlotNumber) & ", "
            Else
                eArrayCellContents = eArrayCellContents & eArray(UsedeArraySlotNumber)
            End If
        Next
    End If
'
    MsgBox "The " & UsedCellsIneArray & " values stored in 'eArray' are ... " & eArrayCellContents
End Sub

 

[johnnyL]

Better yet change the following line in the latest code from:

                ReDim eArray(lr)

To:

                ReDim eArray(1 to lr)

 

[kelly mort]

It displays the message box alert:

The 0 values stored in 'eArray' are ...

 

[johnnyL]

hmmm. I will take another stab at it later on if noone else steps in. Sorry I couldn't help you right now.

 

[kelly mort]

The reason why I want to load the e to the string or the array is to be able to later return the smallest value from that string or array with a script which I already have.

For example the

Application.Min(ray)

Since I am facing hard times figuring out how to properly load to the array or string, can someone help me out with the kind of output I am having currently, to get the smallest number?

In case my output (data stored in ray) is 2 6 1 8 7

How do I get the smallest number (which is 1)?

Sub GetMissingNum ()
    Dim e&, lr&, sht As Worksheet, ray 
       ray = ""
        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))
                       
                      ray = ray & e & " " 
                  End With 
                  End If
            End Select 
         Next sht 

         ray = Trim (ray)

         MsgBox ray
End Sub

 

[johnnyL]

...
         Next sht
         MsgBox ray
End Sub

Upon several tests, I have been able to get closer than I imagined.

Now, my MsgBox alert is giving me:
2,6,1,8,7,

Which are the exact missing numbers of the respective sheets are indicated in my sample code above.
Which means that the variable ray = 2,6,1,8,7,

How do I make it:

ray = ("2, 6, 1, 8, 7")
...

To answer that question you can replace the code of:

         Next sht
         MsgBox ray
End Sub

with the following code:

        Next sht
        ray = "(" & """" & Left(ray, Len(ray) - 1) & """" & ")"   ' Delete last comma, and insert front and back end data
        MsgBox ray

That should get you your desired 'ray = ("2, 6, 1, 8, 7")'