I am writing a long macro User defined function which will be used in
multiple workbooks. it uses multiple inputs from the workbooks all of which
will be in the same row. I have been using the =XXX(a,b,c,d,e,f,g,...) but
would prefer no or just one transfer variable. The columns may change but I
have a method of determining which column I need. My question is "How do I,
either determine which row The particular UDF is in, or call for a value x
number of columns from the cell but in the same row?" (I know I could just
insert a column with "=ROW()" and reference it <and have done so in the past
but would like a better method
[I am using EXCEL 2002]

set rng = Application.Caller
set rng1 = rng.parent.Cells(rng.row,"F")

--
Regards,
Tom Ogilvy


"bj" wrote in message
...
I am writing a long macro User defined function which will be used in
multiple workbooks. it uses multiple inputs from the workbooks all of
which
will be in the same row. I have been using the =XXX(a,b,c,d,e,f,g,...)
but
would prefer no or just one transfer variable. The columns may change but
I
have a method of determining which column I need. My question is "How do
I,
either determine which row The particular UDF is in, or call for a value x
number of columns from the cell but in the same row?" (I know I could just
insert a column with "=ROW()" and reference it <and have done so in the
past
but would like a better method
[I am using EXCEL 2002]

You should always include all input in the argument list, otherwise Excel
doesn't know when to recalculate. But it can be a range instead of single
cells. In the function you'll then have to pick the right cells from the
range argument.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"bj" wrote in message
...
I am writing a long macro User defined function which will be used in
multiple workbooks. it uses multiple inputs from the workbooks all of
which
will be in the same row. I have been using the =XXX(a,b,c,d,e,f,g,...)
but
would prefer no or just one transfer variable. The columns may change but
I
have a method of determining which column I need. My question is "How do
I,
either determine which row The particular UDF is in, or call for a value x
number of columns from the cell but in the same row?" (I know I could just
insert a column with "=ROW()" and reference it <and have done so in the
past
but would like a better method
[I am using EXCEL 2002]

Thank you exactly what I needed.

"Tom Ogilvy" wrote:

set rng = Application.Caller
set rng1 = rng.parent.Cells(rng.row,"F")

--
Regards,
Tom Ogilvy


"bj" wrote in message
...
I am writing a long macro User defined function which will be used in
multiple workbooks. it uses multiple inputs from the workbooks all of
which
will be in the same row. I have been using the =XXX(a,b,c,d,e,f,g,...)
but
would prefer no or just one transfer variable. The columns may change but
I
have a method of determining which column I need. My question is "How do
I,
either determine which row The particular UDF is in, or call for a value x
number of columns from the cell but in the same row?" (I know I could just
insert a column with "=ROW()" and reference it <and have done so in the
past
but would like a better method
[I am using EXCEL 2002]

In the particular case where this UDF will be used, no recalcs should ever be
needed. The spreadsheet is generated elsewhere, and the format is for their
convienience. The appropriate columns are not adjacent, and the prefered
place to put the column using this function is in the middle of the lookup
columns. There are other users who want the columns kept in the order they
they come in. If anyone changes any data a new spreadsheet will be provided,
I agree with you for general situations.

"Niek Otten" wrote:

You should always include all input in the argument list, otherwise Excel
doesn't know when to recalculate. But it can be a range instead of single
cells. In the function you'll then have to pick the right cells from the
range argument.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"bj" wrote in message
...
I am writing a long macro User defined function which will be used in
multiple workbooks. it uses multiple inputs from the workbooks all of
which
will be in the same row. I have been using the =XXX(a,b,c,d,e,f,g,...)
but
would prefer no or just one transfer variable. The columns may change but
I
have a method of determining which column I need. My question is "How do
I,
either determine which row The particular UDF is in, or call for a value x
number of columns from the cell but in the same row?" (I know I could just
insert a column with "=ROW()" and reference it <and have done so in the
past
but would like a better method
[I am using EXCEL 2002]