How to find the coordinates of a named range in Basic?

[ChrisDancer]

Pretty much what it says in the question.
Let’s say I have a named range which consists of cells B11:B20 (but this may change if the spreadsheet is altered). Is it possible to find the cell coordinates from the name alone? Actually just finding the first cell would be OK.

Here’s the reason I want to do this. If anyone can point me to a solution I’d be grateful.

I want to multiply all the cells in a named range by the value in one other cell.

Like this: MyRange = MyRange * MyValue

I have figured out how to access the data in the cells using .getData() but the way this returns an object consisting of an array of arrays seems clunky, there has to be an easier way! The range is always going to be 10 cells in a column so if I can find the coordinates of the top one I can do the rest by recalculating the value of each cell individually in a loop.

[ChrisDancer]

Aha, I’ve found a way…

Range = ThisComponent.Sheets(0).getCellRangebyName(“list_u_prices”)

s = Range.AbsoluteName()

returns a string like “$Sheet1.$C$20:$C$29”
from which I can extract the coordinates of all the cells in the range.

[Lupp]

It seems you are using the term “coordinates” for a description of the abstract position of a cell in the sheet by columnindex and rowindex (both 0-based!).
To parse the string .AbsoluteName for the needed information is dispensable. The info is directly accessible from the .RangeAddress structure. An object for a single cell also has this property, but in addition it has .CellAddress.
If you actually need to get the cells of a range one by one use mySheet.getCellByPosition(ci, ri) inside a respective loop. If no cell attributes need to be accessed, you may get a more efficient code using the .DataArray.
Take in account that the array property .Data only handles numbers. For cells not having a numeric value this array only contains the code for NaN. Elements of .DataArray you can also ask for their TypeName. And you can return changed numbers an strings to the range by myRange.setDataArray(DataArray).

[ChrisDancer]

Thanks for your answer, I’ll try those solutions.