Using a relative cell reference in a function

[john williams]

Hi, apologies in advance for what I'm sure is a too-simple question, but I can't work this out!

I want to use a relative cell reference in a function - so say the function is used by cell A1 and I want to return the value of cell A2. If I make the function:

test = Worksheets("Sheet1").Range("A2").Value

then it works. But if I use

test = Worksheets("Sheet1").Range("RC[1]").Value

then it doesn't - it returns #VALUE!

Can anyone help me out with the correct syntax?


Thanks

[sweep]

Hi John,

I'm not sure exactly what you're after doing, but you might want to try the .offset method

Please Login or Register  to view this content.

will return the value of A1, offset by 1 row and 0 columns to the variable test

[john williams]

Dave - What I want is to refer to the function from a whole load of different cells, so I can't use any absolute references. basically I want the function to know which cell is calling it, and be able to return a value from a cell relative to it.

[sweep]

in the case of a user defined function, the cell calling the function is given with the ActiveCell object eg.,

Please Login or Register  to view this content.

and you can refer to cells relative to this, as discussed, with the offset method. eg.,

Please Login or Register  to view this content.

Dave

[john williams]

Thanks Dave, but I still can't get it to work - the activecell reference seems very strange.

I attach a small demo to show where I'm at.

[Richard Schollar]

Hi John

For this you need to use Application.Caller so that the cell from which you offset is the worksheet cell that contains the function ie:

Please Login or Register  to view this content.

Is this for a more complicated function (ie this is just an example)? I can't see a great deal of use for this function.

Best regards

Richard

[john williams]

Richard - thank you, that's just what I needed!

Now that I've got the syntax right I will be making more complex use of it, yes.

Thanks


John