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.

 

[Marc L]

​

Just take a glance to the VBA help of Currency data type for example …​

 

[Marc L]

Microsoft disagrees. But wouldn't be the first or last time they get something wrong.

As your link is not for VBA ! Do not confuse VB & VBA …​

 

[johnnyL]

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.

To load 'e' into an array, how about:

eArray(e) = e

You would have to dim the eArray, That code may or may not yield the results you desire.

 

[johnnyL]

As your link is not for VBA ! Do not confuse VB & VBA …​

Ahhhh. Good catch @Marc L. Thank you!

This list better?

'Identifier type character  Data type                   Example
'           %               Integer                     Dim L%
'           &               Long                        Dim M&
'           @               Currency                    Dim cProfit@
'           !               Single                      Dim Q!
'           #               Double                      Dim X#
'           $               String                      Dim V$ = "Some String"
'           ^               LongLong (64 bit versions)  Dim llDiscount^

 

[Marc L]

​

For sure …​

 

[kelly mort]

To load 'e' into an array, how about:

eArray(e) = e

You would have to dim the eArray, That code may or may not yield the results you desire.

How do I Dim the eArray?

 

[kelly mort]

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 & ","
                      'ray = ray & e & " " 
                  End With 
                  End If
            End Select 
         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")
Or
ray = Array (2, 6, 1, 8, 7)
???????

 

[johnnyL]

@kelly mort Try the following code to test the storage of values into an array:

Sub GetMissingNum()
'
    Dim e&, lr&, eArraySlotNumber&, UsedCellsIneArray&, UsedeArraySlotNumber&, sht  As Worksheet
'
    Dim eArrayCellContents  As String
    Dim eArray(1 To lr)     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(1 To lr) As Variant
'
                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

Please report back with what the messagebox says.

 

[kelly mort]

@johnnyL
I get the error message: compiler error

Constant expression required

 Dim eArray(1 To lr)     As Variant

Then the lr is highlighted

 

[johnnyL]

@johnnyL
I get the error message: compiler error

Constant expression required

Dim eArray(1 To lr)     As Variant

Then the lr is highlighted

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'