Thread: How to reference cell properties where function is written?

Hi, new to this forum, and pretty much new to vba programming.

I'm using Excel 2007, and I have a particular problem re. referencing the "current cell" data

My formula requires that I make reference to the cell property row and column.

I can write my formula so that it works correctly, however I would like the formula to update if the spreadsheet changes.

The trouble is, I have references the cell propery using activecell.cell and activecell.column.

If I make the formula update then if my cursor isn't on the cell where the formula is, the activecell references are wrong, and the formula then displays the wrong result.

Is there any way that I can reference the cell properties, no matter where the cursor is, so that my formula will work if I make it updateable?

Many thanks in advance.

Welcome,

Without seeing the actual code, it is hard to give you advice. Please copy, paste your code in a post, select it and press the # sign in the (advanced) editor.

Without seeing the code, the ActiveCell changes if you use code like

Code:

range.select

best is to make your own variable like

Code:

Dim MyRng as Range

and reference this to the range you a working with

Code:

Set MyRng = range("A1:B2")
  With MyRng
  'etc
  End With

hope this helps

Thanks, that is pretty much the crux of the problem.

Apologies - I wrote "formula" in the title, where I meant to put "function".

I need the formula to have a different value for "column", for example, if the formula is in cell B2, than if it's in cell B3.

I want the formula to be in many cells within a sheet, and for each of them to update

I currently have

Code:

Function Ratio
.
.
.
RAT = ActiveCell.Row
.
.
.
End function

The first time I imput the formula in a cell, activecell is the correct loation, however if I update the sheet, then this recalculatees, and activecell is not correct.

I would like the function to always reference the cell that it is in, rather than "activecell".

Maybe a little more clarification,

I don't want to reference

Code:

activecell.row

I want to reference

Code:

cell_that_function_is_in.row

Lepista, I added code tags to your post. Please have a look at the forum rules to find out why you need to use them if you post code.

What you need is Application.Caller:

Code:

RAT = Application.Caller.Row

Cheers RS - spot on!

Lepista, please don't quote whole posts. Why quote at all, if you are responding to the post directly above???