How to refer to all elements in an array variable

Question

I want to be able to refer to all elements in an array variable.

Is this possible?

'For example, if I did this:
YD = "=COUNTIFS('Sheet1'!C2,M(k,i),'Sheet1'!C4,""*""&F(l))"

'I want 'Sheet1'!C2 & 'Sheet1'!C4 to be arrays upon which I refer to all _
'the elements in that array.

Also, how do I declare an array variable public for all subs to use?

Option Explicit

Public mcco As Worksheet
Public mcfc As Worksheet
Public mcfb As Worksheet
Public mcfv As Worksheet
Public CVTR As Long
Public FCBR As Long
Public FBCC As Long
Public FCMC As Long
Public MBSA As Variant
Public FTNA As Variant
Public FTVA As Variant
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim s As Long
Dim XD
Dim YD
Sub MatrixSet()

Set mcco = Workbooks("PERSONAL.xlsb").Worksheets("Sheet1")
Set mcfc = Workbooks("PERSONAL.xlsb").Worksheets("Sheet2")
Set mcfb = Workbooks("PERSONAL.xlsb").Worksheets("Sheet3")
Set mcfv = Workbooks("PERSONAL.xlsb").Worksheets("Sheet4")
'mcco = Sheet1
'mcfc = Sheet2
'mcfb = Sheet3
'mcfv = Sheet4

'Array Variables
' maybe use this function to do these two
'Join( SourceArray, [Delimiter] )
''Sheet1'!C2
''Sheet1'!C4

CVTR = mcco.Cells(Rows.Count, 2).End(xlUp).Row
FCBR = mcfc.Cells(Rows.Count, 2).End(xlUp).Row - 1
FBCC = Application.WorksheetFunction.Max(mcfc.Columns(6)) + 1
FCMC = mcfc.Cells(1, Columns.Count).End(xlToLeft).Column
'CVTR = Shee1 Row size
'FCBR = Sheet2 & Sheet3 Row Size
'FBCC = Sheet3 Max Columns. Must be done after calculating Column 6
'FCMR = Sheet2 Max Column

''Sheet3'!R" & i & "C" & k & " = MBSA(k,i)
MBSA = WorksheetFunction.Application.Transpose(Range(mcfb.Cells(1, 1), mcfb.Cells(FCBR, FBCC)))
'MBSA = Sheet2 & Sheet3 Array

FTNA = WorksheetFunction.Application.Transpose(WorksheetFunction.Application.Transpose(Range(mcfc.Cells(1, 1), mcfc.Cells(1, FCMC))))
'Use this in place of 'Sheet2'!R1C" & j & "

    For l = 3 To FCMC
        XD = "=SUBSTITUTE(SUBSTITUTE(MID(""" & FTNA(l) & """,FIND(""*"",SUBSTITUTE(""" & FTNA(l) & """,""("",""*"",(LEN(""" & FTNA(l) & """) - LEN(SUBSTITUTE(""" & FTNA(l) & """,""("","""")))))+1,LEN(""" & FTNA(l) & """)),"")"",""""),""("","""")"
        Cells(l, 1).Formula = XD
        mcfv.Cells(l, 1).Value = mcfv.Cells(l, 1).Value
    Next l

FTVA = WorksheetFunction.Application.Transpose(Range(mcfv.Cells(1, 1), mcfv.Cells(FCMC, 1)))
'Use this in place of 'Sheet2'!R1C" & j & "

For i = 2 To CVTR
    For l = 3 To FCMC
        For k = 2 To FBCC
            'I need Sheet1C2 to be an array. How?
            YD = "=COUNTIFS('Sheet1'!C2,""" & MBSA(k,i) & """,'Sheet1'!C4,""*""&""" & FTVA(l) & """)"
        Next k
    Next l
Next i

Stop

End Sub

As seen above, I'm trying to use YD to count all the rows in Sheet2 Column 2 with the values in the array MBSA. However, I also want Sheet 2 Column 2 to also be an array as this makes the process go far faster. How do I do this?

What do you actually want to do? Can you give us some more context? — BigBen, May 09 '19 at 02:29
So, say that one array was {1,2,3,4,5,6,7,8,9,5,3,6,8652,6,6,4,258} and I wanted to count how many times the number 6 occurred in that array. How would I do that? — Edward Azar, May 09 '19 at 02:39
Where is this array from? Also, see [this answer](https://stackoverflow.com/questions/2722146/how-do-i-declare-a-global-variable-in-vba) for your second question. — BigBen, May 09 '19 at 02:42

How to refer to all elements in an array variable

0 Answers0