big stupid question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Wu - 17 May 2008 06:09 GMT

In a marco,
If I write ---> EN = cells(1,1)
then, EN will equal to the data of cells(1,1)

The problem is :
The cursor will stop at different cell, not certain.
May be at cells(2,2), cells(3,10), cells(10,2)............., every time is
different.
So, I want to get the location of the cell, then set EN is equal to that cell.

How to write in marco to get the location of cells.

Reply to this Message

Rick Rothstein (MVP - VB) - 17 May 2008 07:16 GMT

Try...

EN = ActiveCell.Value

Rick

> In a marco,
> If I write ---> EN = cells(1,1)
[quoted text clipped - 8 lines]
>
> How to write in marco to get the location of cells.

Reply to this Message

Gord Dibben - 17 May 2008 16:43 GMT

If you want the location(address)

Sub goop()
EN = ActiveCell.Address
'MsgBox "address is " & EN
End Sub

Otherwise, see Rick's reply for returning the value.

Gord Dibben MS Excel MVP

>In a marco,
>If I write ---> EN = cells(1,1)
[quoted text clipped - 7 lines]
>
>How to write in marco to get the location of cells.

Reply to this Message

dksaluki - 17 May 2008 17:15 GMT

Are you looking for a certain value, and then you'd like to get the
address of that cell? "...cursor will stop at different cell, not
certain..."
Or are you just wanting the address of whatever cell is active at the
time?

If you're looking for something, and then would like to get the
address of that cell, you will need a Cells.Find(.......) function.
Then you could make an if statement to display cell address.

Dim mySearch as Range

On Error Resume Next
Set mySearch = Cells.Find("____")

If mySearch.Value = _____ Then
MsgBox (mySearch.Address)
End If

....something to that effect. Have to debug a little better, but
that's basically it.

dk

Reply to this Message

Dave Peterson - 17 May 2008 17:33 GMT

Check one of your other posts, too.

> In a marco,
> If I write ---> EN = cells(1,1)
[quoted text clipped - 7 lines]
>
> How to write in marco to get the location of cells.

Signature

Dave Peterson

Reply to this Message