Relative cell position in its own CurrentRegion

[qsdf]

Hello!

I am back to VBA for a personal project and I am having a hard time to get what I want with ranges.

I want to let the user choose a cell, expand it to its CurrentRegion to get the table in which the cell is located, and then I am looking for a way to get the relative position of the cell in this new Range.

First of all, I am having an error when trying to set a variable as the CurrentRegion range

Dim myTable As Range
myTable = ActiveCell.CurrentRegion

I tried various ideas (using Excel.Range variable type, using Range(ActiveCell.CurrentRegion), using ActiveCell.CurrentRegion.Cells, etc...) but without any success: I always end up with "object variable or with block variable not set".

In the end, I would like to obtain the relative position of the cell in its current region: if the cell C5 is in CurrentRegion A2:F12, I would like to know that it is in the 4th row and 2nd column. Is there an easy way to work with cell/range properties in a new reference (not the whole sheet)?

If anyone has any idea on that matter, I would be glad to learn more about it.

Thank you,
B

[Neil_]

Please Login or Register  to view this content.

[millz]

Maybe this:

Please Login or Register  to view this content.

[qsdf]

Thank you!

I was missing the "Set" at the beginning.

And thank you for the relative position part as well, it's going to be useful!