How to find the cell given x and y worksheet coordinates

[Guest]

I know how to find .top and .right of a given cell

Does someone know if the opposite is doable

Thanks a lot. Stefano

 

[GJones]

Hi Stefano;

One idea might be to take the top and the take the
measurement for the activecell.row and column width and
height and add them to the top. This would give you the
the other values.

Thanks,

Greg

 

[Michel Pierron]

Hi Stefano;
With ActiveCell
MsgBox "Cell :" & vbTab & .Address(False, False) _
& vbLf & "Top :" & vbTab & .Top & vbLf _
& "Right :" & vbTab & .Offset(0, 1).Left
End With

MP

 

[Guest]

Sorry guys, but I deduct from your replies that I definitely did not express myself clearly enough. Sorry. Here I try again

For a given (or known) cell, I can find the x and y coordinates by invoking the .left and .top properties of the range. Typically this is

x=thisworkbook.worksheets(1).cells(10,10).lef
y=thisworkbook.worksheets(1).cells(10,10).to

What I need is the opposite i.e. find the range object, given 2 integers that represent the x and y in the same units as are expressed .left and .top above. Do you know how to find that 1-cell range object given those x and y?

 

[Otto Moehrbach]

Stefano
It is not clear what you want. When you say "the x and y coordinates"
do you mean the column number and the row number? When you say you want to
"find the cell", what do you want? Do you want that cell selected? Do you
want just the cell address? Post back with a clearer explanation of what
you want. For instance, give an example of the coordinates and what you
want to happen/see. HTH Otto

 

[Stefano Gatto]

Thanks Otto for your interest to better understand my problem.

Vic Eldridge replied to another of my posts above and told me to use the
TopLeftCell property and this is what I needed.

I wanted to implement a function returning the range object of the cell
where a botton has its upper left corner, when given by its name (like the
string "Button 1" for example).

For example: imagine there is a button named "Button 1" and having its top
left corner in cell B10, I wanted a function returning the range object of
cell B10, when given "Button 1" as parameter.

This would allow me to create a button and attach a macro to it that writes
the current time (Now) on the cell where the button itself is located. Then
I can copy and paste this cell (and the button that goes with it) how many
times I want, by preserving the button's capability to write to the cell
where the new button is pasted... I hope this time I am clear.

Thanks and have a nice day.

Stefano

PS: I made the mistake below to mention .right - I actually meant .left

 

[Stefano Gatto]

Something like this is independent from the button name, so you can have it
calle by any button, but it will set the rngcell to the cell where the
pressed button resides:

set
rngCell=thisworkbook.worksheets(1).buttons(application.caller).topleftcell

Stefano

 

[Otto Moehrbach]

Sorry but I can't help you with that. Hopefully someone else will respond
and help you with it. Otto