user defined function row number
 

I would like to know how to utilize the row information of a cell using a
user defined function (UDF). For example if I wanted to pull data from a
cell 4 columns to the left of the cell with the UDF how would I format the
VBa statement in a user defined function. An offset function would also work
but I believe the offset needs a selected cell to be offset from??

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row information
of a cell using a
user defined function (UDF). For example if I wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.

Hi

When the column difference is always same, then p.e. into cell E1 ebter the
formula
=A1
When you copy the formula to F1, it will refer to cell B1, etc. - always 4
columns to left.

Another way is to use OFFSET - p.e. into E1 enter the formula
=OFFSET(E1,0,-4)
returns also the value from A1. The difference is, that for 2nd parameter
you can use a function too.


Arvi Laanemets


"bj" wrote in message
...
I would like to know how to utilize the row information of a cell using a
user defined function (UDF). For example if I wanted to pull data from a
cell 4 columns to the left of the cell with the UDF how would I format the
VBa statement in a user defined function. An offset function would also
work
but I believe the offset needs a selected cell to be offset from??

Unfortunately I get a "#name" in the cells using the following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row information
of a cell using a
user defined function (UDF). For example if I wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.

No need to use a UDF. Just copy the formula into a
worksheet cell.

Jason

-----Original Message-----
Unfortunately I get a "#name" in the cells using the
following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row
information
of a cell using a
user defined function (UDF). For example if I
wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF
how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.


.

Thank you but;

I am working with a several hundred line macro based udf on a worksheet with
several thosand lines, and with five inputs into the macro function. I have
methods for finding the appropriate columns, which vary from Worksheet to
worksheet, and if I have to I will insert a column with row numbers and
reference them in the macro function input, but It seems as though there
should be a more elegant method of doing what I want.

"Arvi Laanemets" wrote:

Hi

When the column difference is always same, then p.e. into cell E1 ebter the
formula
=A1
When you copy the formula to F1, it will refer to cell B1, etc. - always 4
columns to left.

Another way is to use OFFSET - p.e. into E1 enter the formula
=OFFSET(E1,0,-4)
returns also the value from A1. The difference is, that for 2nd parameter
you can use a function too.


Arvi Laanemets


"bj" wrote in message
...
I would like to know how to utilize the row information of a cell using a
user defined function (UDF). For example if I wanted to pull data from a
cell 4 columns to the left of the cell with the UDF how would I format the
VBa statement in a user defined function. An offset function would also
work
but I believe the offset needs a selected cell to be offset from??

Thanks but please see the response I gave to Arvi Laanemets

"Jason Morin" wrote:

No need to use a UDF. Just copy the formula into a
worksheet cell.

Jason

-----Original Message-----
Unfortunately I get a "#name" in the cells using the
following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row
information
of a cell using a
user defined function (UDF). For example if I
wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF
how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.


.

When you post in .worksheet.functions, you'll usually get a worksheet function
response.

..Programming may have been a better newsgroup to post.

Option Explicit
Function myfunction()
application.volatile
myfunction = Application.Caller.Offset(0,-4).Value
End Function
(no validation at all)

But functions like this can give you a false sense of security. If you don't
pass all the parms you need to the function, then excel won't know when to
recalculate.

You can add
application.volatile
to the top of the code, but that means your function (all of them) will
recalculate when excel recalculates.

This is usually overkill--the cells don't change that often and can slow down
your workbook (if you use lots of these UDF's.)

Better to pass it the cell that is used:

If the formula is in F19:
=myfunction(F15)
Option Explicit
Function myfunction(rng as range)
myfunction = rng.cells(1).value
End Function




bj wrote:

Unfortunately I get a "#name" in the cells using the following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row information
of a cell using a
user defined function (UDF). For example if I wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.



--

Dave Peterson

Mea Culpa:

I actually realized that after I posted it, but did not want to multi-
post, so did not post it there (as of today anyway), and I normally see such
good information in this site that I thought it worthwhile to leave it here.
(Plus I use enough user defined macro functions to just think of them as
functions. )

"Dave Peterson" wrote:

When you post in .worksheet.functions, you'll usually get a worksheet function
response.

..Programming may have been a better newsgroup to post.

Option Explicit
Function myfunction()
application.volatile
myfunction = Application.Caller.Offset(0,-4).Value
End Function
(no validation at all)

But functions like this can give you a false sense of security. If you don't
pass all the parms you need to the function, then excel won't know when to
recalculate.

You can add
application.volatile
to the top of the code, but that means your function (all of them) will
recalculate when excel recalculates.

This is usually overkill--the cells don't change that often and can slow down
your workbook (if you use lots of these UDF's.)

Better to pass it the cell that is used:

If the formula is in F19:
=myfunction(F15)
Option Explicit
Function myfunction(rng as range)
myfunction = rng.cells(1).value
End Function




bj wrote:

Unfortunately I get a "#name" in the cells using the following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row information
of a cell using a
user defined function (UDF). For example if I wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.



--

Dave Peterson

If you think you want to move the thread to another newsgroup, you can post a
followup that says that you moved it to .programming.

I think that would make most people happy enough to know you didn't mean to
multipost.



bj wrote:

Mea Culpa:

I actually realized that after I posted it, but did not want to multi-
post, so did not post it there (as of today anyway), and I normally see such
good information in this site that I thought it worthwhile to leave it here.
(Plus I use enough user defined macro functions to just think of them as
functions. )

"Dave Peterson" wrote:

When you post in .worksheet.functions, you'll usually get a worksheet function
response.

..Programming may have been a better newsgroup to post.

Option Explicit
Function myfunction()
application.volatile
myfunction = Application.Caller.Offset(0,-4).Value
End Function
(no validation at all)

But functions like this can give you a false sense of security. If you don't
pass all the parms you need to the function, then excel won't know when to
recalculate.

You can add
application.volatile
to the top of the code, but that means your function (all of them) will
recalculate when excel recalculates.

This is usually overkill--the cells don't change that often and can slow down
your workbook (if you use lots of these UDF's.)

Better to pass it the cell that is used:

If the formula is in F19:
=myfunction(F15)
Option Explicit
Function myfunction(rng as range)
myfunction = rng.cells(1).value
End Function




bj wrote:

Unfortunately I get a "#name" in the cells using the following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row information
of a cell using a
user defined function (UDF). For example if I wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.



--

Dave Peterson


--

Dave Peterson

Thanks for the suggestion. I will do that.

"Dave Peterson" wrote:

If you think you want to move the thread to another newsgroup, you can post a
followup that says that you moved it to .programming.

I think that would make most people happy enough to know you didn't mean to
multipost.



bj wrote:

Mea Culpa:

I actually realized that after I posted it, but did not want to multi-
post, so did not post it there (as of today anyway), and I normally see such
good information in this site that I thought it worthwhile to leave it here.
(Plus I use enough user defined macro functions to just think of them as
functions. )

"Dave Peterson" wrote:

When you post in .worksheet.functions, you'll usually get a worksheet function
response.

..Programming may have been a better newsgroup to post.

Option Explicit
Function myfunction()
application.volatile
myfunction = Application.Caller.Offset(0,-4).Value
End Function
(no validation at all)

But functions like this can give you a false sense of security. If you don't
pass all the parms you need to the function, then excel won't know when to
recalculate.

You can add
application.volatile
to the top of the code, but that means your function (all of them) will
recalculate when excel recalculates.

This is usually overkill--the cells don't change that often and can slow down
your workbook (if you use lots of these UDF's.)

Better to pass it the cell that is used:

If the formula is in F19:
=myfunction(F15)
Option Explicit
Function myfunction(rng as range)
myfunction = rng.cells(1).value
End Function




bj wrote:

Unfortunately I get a "#name" in the cells using the following udf

Function tes()
tes = Offset(INDIRECT("rc", 0), , -4)
End Function

with items in the apppropriate columns

"Jason Morin" wrote:

=OFFSET(INDIRECT("rc",0),,-4)

HTH
Jason
Atlanta, GA

-----Original Message-----
I would like to know how to utilize the row information
of a cell using a
user defined function (UDF). For example if I wanted
to pull data from a
cell 4 columns to the left of the cell with the UDF how
would I format the
VBa statement in a user defined function. An offset
function would also work
but I believe the offset needs a selected cell to be
offset from??
.



--

Dave Peterson


--

Dave Peterson

I have transfered this thread over to .programing. Thanks to everyone who
tried to help me.