VBA -- Populate array with cell references (addresses)

[NerdyGirl]

Hi everyone,

Hopefully this is a pretty simple fix, as I'm just beginning to become familiar with arrays in VBA. I'm looking to fill an array with the cell addresses using a For loop. I'm wondering if the problem is occurring because the arrays are by default Variant (?). Thanks for your help!

Here's a portion of my code:

Sub PartialCode

   Dim x1 As Long
   Dim x2 As Long
   Dim x3 As Long

    x1 = Application.CountIf(Range("B2", Range("B2").End(xlDown)), "C")
    x2 = Application.CountIf(Range("B2", Range("B2").End(xlDown)), "F")
    x3 = Application.CountIf(Range("B2", Range("B2").End(xlDown)), "M")
    
    Dim childLC()
    Dim femaleLC()
    Dim maleLC()

    ReDim childLC(x1)
    ReDim femaleLC(x2)
    ReDim maleLC(x3)

For i = 3 To NumCol 'NumCol defined earlier in code
          
            c = 1
            f = 1 ' used later in code
            m = 1 ' used later in code
            
            For b = 2 To NumRow 'NumRow defined earlier in code
            
            MsgBox wsLC.Cells(b, i).Address ' this returns what I want as an element of the array childLC
            
                If wsLC.Cells(b, 2) = "C" Then
                    Set childLC(c) = wsLC.Cells(b, i).Address(RowAbsolute:=f, ColumnAbsolute:=f) 
                           'Ideally something like the .Address code above would fill the array with the cell addresses
                    Set childBC(c) = wsBC.Cells(b, i).Address(RowAbsolute:=f, ColumnAbsolute:=f)
                    c = c + 1




'''continued code....''''
                 End If
             Next b
 Next i

End Sub

 

[MickG]

This basically works , I think you had used the "Set" statement on an array (set statement used for Object)

Dim x1 As Long
Dim x2 As Long
Dim x3 As Long
Dim i, numcol, c, f, m, b, numrow
numcol = 1: numrow = 30
    x1 = Application.CountIf(Range("B2", Range("B2").End(xlDown)), "C")
    x2 = Application.CountIf(Range("B2", Range("B2").End(xlDown)), "F")
    x3 = Application.CountIf(Range("B2", Range("B2").End(xlDown)), "M")
    
    Dim childLC()
    Dim childBC()
    Dim femaleLC()
    Dim maleLC()

    ReDim childLC(x1)
    ReDim childBC(x1)
    ReDim femaleLC(x2)
    ReDim maleLC(x3)

For i = 1 To numcol 'NumCol defined earlier in code
          
            c = 1
            f = 1 ' used later in code
            m = 1 ' used later in code
            
            For b = 2 To numrow 'NumRow defined earlier in code
            
            MsgBox Cells(b, i).Address ' this returns what I want as an element of the array childLC
            
                If Cells(b, 2) = "c" Then
                     childLC(c) = Cells(b, i).Address(RowAbsolute:=0, ColumnAbsolute:=0)
                     childLC(c) = Cells(b, i).Address
      
                           'Ideally something like the .Address code above would fill the array with the cell addresses
                     childBC(c) = Cells(b, i).Address(RowAbsolute:=f, ColumnAbsolute:=f)
                    c = c + 1




'''continued code....''''
                 End If
             Next b
 Next i