How can a formula get the location of the cell it is placed in?

  • Thread starter Thread starter Bernard
  • Start date Start date
B

Bernard

Hi-

Is there any way a formula can get the location of the cell it is placed
in? I need the row and column of the cell at recalculation time, not at the
time formula was entered, so ActiveCell won't do the job.

Thanks in advance for any help.
 
Bernard,

Application.Caller will return a Range reference pointing to the
cell containing the formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Hi
=ROW() -> gives you the row number of the cell this formula is in
=COLUMN() -> the column number
=ADDRESS(ROW(),COLUMN()) the cell reference
 
Public MyFunction( )
set rng = Application.Caller
rw = rng.row
col = rng.column
MyFunction = rng.address
End Function
 
Back
Top