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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.