cell reference

Posted by J.R. on December 21, 2000 10:34 AM

is there a way to find the location or position of a
cell?

I can use cellName = ActiveCell.Address within VBA but
I would like to enter a formula into the spreadsheet
itself to return the position of the highlighted
cell.

Thanks

Posted by Tim Francis-Wright on December 21, 2000 11:04 AM

You'll need to set up a user-defined function
in a module:

Public Function activecel()
Application.Volatile
' first syntax returns B2
' second syntax returns Sheet1!B2
' activecel = activecell.Address
activecel = activecell.Parent.Name & "!" & activecell.Address

End Function

And... you'll want the following in the
ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Sh.Calculate ' prevents a syntax error--go figure
Calculate
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Sh.Calculate ' prevents a syntax error--go figure
Calculate
End Sub

If you need something other than the A1-form
of the address, you can modify the activecel
code...

Good luck!

Posted by j.r. on December 23, 2000 8:23 AM

Thanks a lot for the solution. It is a bit more complicated than I thought it would be. I am simply trying to run a loop from the top of my spreadsheet to the last row of data. I wanted to use this function to find the last row without having to set the loop counter higher than needed.

J.R.

Posted by Tim Francis-Wright on December 27, 2000 6:42 AM

If you just need the last row of daat within
VBA, it's *much* easier:
For example see Tony Scala's recent post:
7234.html

that you probably could moficy to meet your needs.

--tim