Sort method - fails

[paolopiace]

I need to sort a Range and place the sorted result into a Variant
matrix.
Then I must copy the matrix into same Excel worksheet, different area.

I did this and it comes out with the error msg #VALUE! in each cell:

Dim theMatrix() As Variant
theMatrix = Range("B2", "H7").Sort(Key1:=Columns(4),
Orientation:=xlSortRows)

While this works just fine:

Dim theMatrix() As Variant
theMatrix = Range("B2", "H7")

Help, Please!....

 

[Andy Pope]

Hi,

Try,
With Range("B2", "H7")
.Sort Key1:=.Cells(1, 4), Orientation:=xlSortRows
theMatrix = .Value
End With

The sort method is not returning a range object which you can then load into
theMatrix.

Cheers
Andy

 

[paolopiace]

Thanks! At least it's doing something but not the expected.

Slightly modified indices:

With Range("A2", "H6")
.Sort Key1:=.Cells(2, 4), Orientation:=xlSortRows
theMatrix = .Value
End With

Doing that the variable theMatrix contains exactly the values of
Range("A2", "H6") with no sorting.

I neet to put in theMatrix the values of Range("A2", "H6") row-sorted
through the values in column "E".

 

[Andy Pope]

Confused.

You want to row sort based on a column?

Manually if you use sort, selecting 'Sort Left to Right' under options,
The sort key will be a Row.

Can you post 2 or 3 records to illustrate what you have and expect.

Cheers
Andy