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


    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.

  • 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


    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!