Array Problem

[mdmackillop]

I'm trying to set an array as follows

Dim MyExt(), s
MyExt = Range("A32", Cells(65536, 1).Offset.End(xlUp)).Value
    For Each s In MyExt
        Debug.Print s
    Next

This works fine as long as there is more than one cell in the range, but falls down if one cell only is found. MyExt is passed to another function for subsequent processing.
Any suggestions? I don't really want to loop through the range of cells, redim the array etc.
MD

[Zack Barresse]

How about a little error handling?

On Error Resume Next
If Err <> 0 Then GoTo whereverHereAfterNext
    'Other code
End If
Next

[Jacob Hilderbrand]

Can you check to see if the value is 1 like this?

If MyExt = 1 Then
'Do Something
Else
For Each s In MyExt 
Debug.Print s 
Next 
End If

[TonyJollans]

Why do you want an array variable? For large ranges there can be performance benefits but, that aside, the range is an array of cells with a default property of .Value. Could you not just pass the range and then it wouldn't matter how many cells were in it?

[mvidas]

Hey MD,

You could also do something like

If Range("A65536").End(xlUp).Row < 33 Then
 '...
Else
 MyExt = Range("A32", Cells(65536, 1).Offset.End(xlUp)).Value
 For Each s In MyExt
  Debug.Print s
 Next
End If

Matt

[sebastienm]

Hi,
Range.Value returns:
- an array of variant if several cells,
- but a single value of the type of the data in the cell if only a single cell.
(you can see it by displaying Typename(rg.value))
Therefore trying to assign a single value to a full array fails.
In this case, force the assignment to an array element and not the full array:

Dim MyExt(), s
Dim rg As Range, rg2 As Range
Set rg = Range("a32")
Set rg2 = Range(rg, rg.EntireColumn.Cells(65536).End(xlUp))
If rg2.Cells.Count = 1 Then
   ReDim MyExt(0 To 0)
   MyExt(0) = rg2.Value
Else
   MyExt = Range(rg, rg.EntireColumn.Cells(65536).End(xlUp)).Value
End If
For Each s In MyExt
    Debug.Print ">" & s
Next

Regards,
Sebastien

[mdmackillop]

Thanks all.
Sebastien's code fitted by need exactly!
MD