Hello

within vb I read some values from my worksheet into a 2-dim table. The values of the cells and their references are kept in this table. After a sort, I want to change the property of the cells that keep the 5 highest values. So, next I want to jump back to these cells by their cell reference that is kept in the second column of my table. Unfortunately, after numerous attempts with e.g. ActiveCell etc... I did not get it to work

Does somebody have an idea

Highly appreciated
ZAkar.

You could use the LARGE() worksheet function and save
yourself some time

If your array is sorted then you could re-size it

Sub GetTop5()

Dim MyArray As Variant 'MUST be variant

'load the data
MyArray = Range("MyList")
MyArray = SortTheArray(MyArray)
' drop it back somewhere
Range("G1:G5") = MyArray
End Sub

SortTheArray is my own function that gets back the top 5
cells


If your array has 5 rows & two columns, where column 1 is
the value and column 2 the cells address

dim Target as Range
Target = Range("G1")
for rw = 0 to 4
Target.Offset(i,0) = mArray(rw,0)
Target.Offset(i,1) = mArray(rw,1)
next





-----Original Message-----
Hello,

within vb I read some values from my worksheet into a 2-
dim table. The values of the cells and their references
are kept in this table. After a sort, I want to change
the property of the cells that keep the 5 highest values.
So, next I want to jump back to these cells by their cell
reference that is kept in the second column of my table.
Unfortunately, after numerous attempts with e.g.
ActiveCell etc... I did not get it to work.

Does somebody have an idea ?

Highly appreciated,
ZAkar.
.

if you use the .address Property to store the cell reference, you can use the Range.Method to retrieve it
MyArray(x, y) = MyCell.Addres
Range(MyArray(x, y)).Select

Thank you very much, Chris. It works now! Also Thanks to Patrick Molloy, appreciate your help a lot

Grtz
ZAkar

----- chris wrote: ----

if you use the .address Property to store the cell reference, you can use the Range.Method to retrieve it
MyArray(x, y) = MyCell.Addres
Range(MyArray(x, y)).Select