I stored a range of cells in an array by the code bellow:

MyArr()=sheet1.range(x1:y1)

X and y change. I have no problem with the array, it works well.

Through a loop I read array values, if lets say a value is "sample", then I need to find a cell's value in a fixed row and the column of "Sample".

I can not find the column number or row number of the stored ranges.

When i type MyArr (1,n) it returns the value but when i try MyArr(1,n). column to get this value column number it doesnt act like an object and gives error. n is related Array index number.

when I store a range in an array, how can I get row or column numbet of this stored range value?

CodePudding user response：

When you do MyArr() = MyRange you're writing a Let Statement, which is assigning Values from an expression to a variable. MyRange accesses its default member which is MyRange.Value. Since it is a multi-cell range, it creates an array of Variant values which get saved into MyArr.

This is a long way to say, you're not putting a range into an array, you're taking the values of the range and putting them into an array. So once you have the values in MyArr, the elements have no members like Range.Row or Range.Column because they are not objects, just values.

If you want to save the Range as an object you can do

    Dim MyRange As Range
    Set MyRange = Sheet1.Range("x1:y1")

If you need to access the values from MyRange you can do either MyRange.Cells(1,1).Value or MyRange.Value()(1,1) which will be the same as what you had in MyArr.

But since it is saved in a Range Object, you have access to all the object members of a range object. For example you can find the row and column - MyRange.Cells(1,1).Row and MyRange.Cells(1,1).Column. Or you can find other values in the same row like MyRange.Cells(1,1).EntireRow.Cells(5).