additional cell address help

[scrabtree]

The Sub DefineCell Address () returns the cell address but
it does not include which sheet the active cell is in?
How can I put the Sheet and Cell address in the reference
cell?

Public MyCellAddr As String
Sub DefineCellAddress()
MyCellAddr = ActiveCell.address
Worksheets("Sheet1").Range("A1").Value = MyCellAddr
End Sub

Sub GotoMyCellAaddress()
Range(MyCellAddr).Select
End Sub

 

[Jan Karel Pieterse]

Hi Scrabtree,

Public MyCellAddr As String
Sub DefineCellAddress()
MyCellAddr = ActiveCell.address
Worksheets("Sheet1").Range("A1").Value = MyCellAddr
End Sub

Sub GotoMyCellAaddress()
Range(MyCellAddr).Select
End Sub

MyCellAddr= "'" & Activecell.parent.name & "'!" & activecell.address

But that would cause the gotomycelladdress sub to fail. Maybe beter to
set an object to the cell:

Public MyCell As Range

Sub DefineCellAddress()
set MyCell = ActiveCell
Worksheets("Sheet1").Range("A1").Value = "'" &
activecell.parent.name & "'!" & Activecell.address
End Sub

Sub GotoMyCellAaddress()
Application.Goto MyCell
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Don Guillett]

why not just use

application.goto sheets("Sheet1").Range("A1")

 

[Don Guillett]

disregard my last post. Helps to read it all.