Cell Location

DragonL · Jul 19th 2004

Hi I have a simple question this time. I'm using Office XP and I would like to know how to store the current cell's location in a variable that I create in VBA.

-DragonL

Jong · Jul 19th 2004

DISREGARD THIS MESSAGE.

Didn't read the initial msg correctly.

Richie(UK) · Jul 19th 2004

Hi DL,

The current cell in Excel is referred to as the ActiveCell. You can store this in a variable like this:[vba]Sub Test()
Dim rngCell As Range

Set rngCell = ActiveCell
MsgBox rngCell.Address

End Sub[/vba]HTH

Anonymous · Jul 19th 2004

I don't mean to be picky, and I think you can get what you asked from Richie's post. But to put the address directly into a variable:

Code

Sub Test()
    Dim CellAddress As String
     
    CellAddress = ActiveCell.Address
    MsgBox CellAddress
End Sub

EDIT: P.S. If you have more than a single cell selected, this will return the address of the top left cell. If you want the address of the entire range selected, use:

CellAddress = Selection.Address

DragonL · Jul 19th 2004

Thanks everyone I am about to try this and see how it works out. Can I use the variable in a goto reference like:

Code

Application.Goto Reference:="var1"

Is this possible or do I have to do something else?

-DragonL

Anonymous · Jul 19th 2004

Son of a gun, I think you can.

Code

Sub Test()
    Dim CellAddress As String
     
    CellAddress = ActiveCell.Address
    If Len(CellAddress) > 0 Then GoTo CellAddress
    Exit Sub
    
CellAddress:
    MsgBox "It Worked"
End Sub

I'd never tried looping to a variable before. Thanks for the lesson.

Cell Location

Participate now!

Share