user defined function row number

[bj]

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??

 

[Jason Morin]

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

HTH
Jason
Atlanta, GA

 

[Arvi Laanemets]

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]

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]

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

Jason

 

[bj]

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.

 

[bj]

Thanks but please see the response I gave to Arvi Laanemets

 

[Dave Peterson]

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]

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]

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]

Thanks for the suggestion. I will do that.

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]

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