CODEWITHSUNDEEP
excelvba
CaryBush
CaryBush

Reputation: 41

empty cells returned by an array

I have been working on this particular problem for sometime and am obviously missing something very simple. I ma trying to create an aray based on a dynamic range in Excel and using the individual elements to compare against another array. The only problem with the attached code is it continues to show empty elements. Any guidance would be appreciated. Part of my overall code attached.

Sub Test_Again()
Dim R As Long
Dim C As Long
Dim List() As Variant
Dim i As Integer

List = Sheets("Sheet11").Range("A2:A17").Value

For R = 1 To UBound(List, 1) ' First array dimension is rows.
    For C = 1 To UBound(List, 2) ' Second array dimension is columns.
        Debug.Print List(R, C)
    Next C
Next R

ReDim List(UBound(List, 1))    
    Do Until i = UBound(List)
               If List(i) = Now() Then                                      
                          End If
               i = i + 1
    Loop
End Sub

Upvotes: 1

Views: 97

Answers (1)

Peter Albert
Peter Albert

Reputation: 17495

The normal Redim will clear your array - unless you use Redim Preserve. However, according to the help:

If you use the Preserve keyword, you can resize only the last array dimension and you can't change the number of dimensions at all. For example, if your array has only one dimension, you can resize that dimension because it is the last and only dimension. However, if your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array.

Therefore, in your case Redim will not help you here. If you want to transfer a two dimensional array to a one dimensional array, you need to do this manually instead:

Sub Test_New()
    Dim lRow As Long, lCol As Long
    Dim vListSource() As Variant, vListTarget() As Variant

    'Assign soure array
    vListSource = Sheets("Sheet11").Range("A2:A17").Value

    'Show full content for debug
    For lRow = LBound(vListSource) To UBound(vListSource) ' First array dimension is rows.
        For lCol = LBound(vListSource, 2) To LBound(vListSource, 2) ' Second array dimension is columns.
            Debug.Print vListSource(lRow, lCol)
        Next lCol
    Next lRow

    'Transfer array to one dimension
    ReDim vListTarget(LBound(vListSource) To UBound(vListSource))
    For lRow = LBound(vListSource) To UBound(vListSource)
        vListTarget(lRow) = vListSource(lRow, LBound(vListSource, 2))
    Next lRow

    'Your check code
    For lRow = LBound(vListTarget) To UBound(vListTarget)
        If vListTarget(lRow) = Now() Then
            'Do something here
        End If
    Next lRow
End Sub

This will copy the first row of your range/array to a one dimensional array and use this for further processing.

However, from your code and question I do not see the advantage of redimming it to one dimension - you could easily do your loop one the two dimensional array - and just look in the first and only column.

Upvotes: 2

Related Questions