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. :help 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