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.

 

[johnnyL]

what does the '&' mean at the end of your Dim variables mean?

 

[kelly mort]

what does the '&' mean at the end of your Dim variables mean?

It means Long

That’s
Dim e As Long, lr As Long

 

[kelly mort]

Cross posted at :

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

Cross posted at : https://www.mrexcel.com/board/threads/how-to-load-the-outcome-of-a-code-into-a-string-or-array-using-vba-by-looping-through-multiple-worksheets.1171919/ Reason : No solution for some time now. Hello guys, I have tried to narrow down what I want to achieve to this level...

www.excelforum.com

Reason:
No solution for some time now.

 

[johnnyL]

It means Long

That’s
Dim e As Long, lr As Long

So it's the short way to write long? LOL

Thank you.

 

[kelly mort]

So it's the short way to write long? LOL

Thank you.

Yes. It's a cheat.

Dim i As Integer or 
Dim i%

Dim Str As String or 
Dim Str$

 

[Marc L]

Hi,​

​

it's just a legacy from its grand father : the BASIC !​

 

[johnnyL]

I found this list that includes a few more:

'Identifier type character  Data type   Example
'           %               Integer     Dim L%
'           &               Long        Dim M&
'           @               Decimal     Const W@ = 37.5
'           !               Single      Dim Q!
'           #               Double      Dim X#
'           $               String      Dim V$ = "Some String"

I prefer the more English (longer way), but it is interesting, I suppose.

 

[Marc L]

​

@ this is not 'Decimal' and as all is in VBA help since last century …​

 

[johnnyL]

​

@ this is not 'Decimal' and as all is in VBA help since last century …​

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