PDA

View Full Version : Solved: Array Problem



mdmackillop
09-01-2004, 01:03 PM
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
09-01-2004, 02:24 PM
How about a little error handling?

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

Jacob Hilderbrand
09-01-2004, 02:27 PM
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
09-01-2004, 02:50 PM
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
09-01-2004, 02:59 PM
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
09-01-2004, 03:37 PM
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
09-01-2004, 04:03 PM
Thanks all.
Sebastien's code fitted by need exactly!
MD