 # Getting The Cell Index For A Function Not The Cells Value

• I am trying to get the cell index from a cell reference passed to a function and I have no idea how.

eg. In excel

=UDF(D4,...,...,)

What I want is to get is the row and column index for cell D4, not whatever is in cell D4, so I can just click the cell in excel, and drag the formula about etc.

• Re: Getting The Cell Index For A Function Not The Cells Value

Not sure I'm with you, but do the ROW() and COLUMN() functions help?

• Re: Getting The Cell Index For A Function Not The Cells Value

Thanks, the problem is they are worksheet functions that are not available to VBA, so I cant use them. Anyone got some bright ideas?

• Re: Getting The Cell Index For A Function Not The Cells Value

Maybe you could use the activecell
Or the range you pass into the UDF

[vba]debug.print activecell.Row,activecell.Column[/vba]

Maybe if you posted the actual function things would be clearer.

[h4]Cheers
Andy
[/h4]

• Re: Getting The Cell Index For A Function Not The Cells Value

Thanks Andy, but all I can get out of that is the cell the formula is in, I dont know how to set a cell passed to the function as the active cell to use activecell.xxxx which would be one way of doing it if its possible.

What I want to do is have in an Excel cell;

=testfunction(B6, X, Y)

And be able to drag it down a column, across a row etc, and have B6 change accordingly. But what I want is the row and column index of the cell as i would get in Excel by typing

=row(B6)
&
=column(B6)

as the result depends on the values X cells above it and Y cells below it. See example below;

• Re: Getting The Cell Index For A Function Not The Cells Value

Quote from nb-

It Looks like simple formula:
=A1/(OFFSET(A1,-par1)+OFFSET(A1,par2))
Are you sure you need UDF?

• Re: Getting The Cell Index For A Function Not The Cells Value

Just replace activecell with the passed object Targetcell.
[vba]Debug.Print targetcell.Row,targetcell.Column [/vba]

[h4]Cheers
Andy
[/h4]

• Re: Getting The Cell Index For A Function Not The Cells Value

Thanks very much Andy :).

& the Okk, thanks, yes the the function shown could be done with a formula, but it is just a grossly simplified example .

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!