Microsoft Excel: Given a Row and a Column I would like the Range reference

I'm inserting a custom user defined function into a cell.

As a parameter to the function is a cell which is the cell in which
the function is located

i.e.

in cell A5 I have the following function

=myCustomFunction(A5)

Now, I want to insert this function automatically into that cell but
the way I have that cell refrenced is in terms of Rows and Columns,
that is A5 = Row 5 Column 1

Is there a way I can do this

Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
somehow' & ")"

Keywords & Tags: given, row, column, range, reference, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/199349/

«« Prev - Next »» 5 helpful answers below.

Except in very special circumstances, a cell cannot contain a function that
refers to it own cell. That is called a circular reference. One can use
circular reference in a properly design worksheet but they are unusual.

Further more no formula (UDF or otherwise) cannot <insert> any formula into
a cell. A formula has the form =FUNCTION_NAME (arguments) and returns a
value (or several values in the case of an array formula)

You need to re-think the task.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" <chris.olekas...gmail.com> wrote in message
news:1190057224.891199.99520...r29g2000hsg.googlegroups.com...
> I'm inserting a custom user defined function into a cell.
> As a parameter to the function is a cell which is the cell in which
> the function is located
> i.e.
> in cell A5 I have the following function
> =myCustomFunction(A5)
> Now, I want to insert this function automatically into that cell but
> the way I have that cell refrenced is in terms of Rows and Columns,
> that is A5 = Row 5 Column 1
> Is there a way I can do this
> Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
> somehow' & ")"
>

bernard | Thu, 22 May 2008 03:59:00 GMT |

Maybe I wasn't clear in my explanation.

Within VBA I wrote my own function with the following signature

Function myCustomFunction(myRange As Range) As Integer
myCustomFunction = 5 ' just for example
End Function

Now I had another sub which I called from a button within a worksheet
that did this

Sub insertMyCustomFunction(xValue As Integer, yValue As Integer)
Cells(xValue, yValue) = "=myCustomFunction("& _
' Now here is where I'm having the issue, I have an xValue and a
yValue that I need to convert to a range i.e. A5
")"
End Sub

Either way, I fixed this by just changing
myCustomFunction(myRange As Range)

to

myCustomFunction(xCoord As Integer, yCoord As Integer)

and just passing the coordinates through the insert sub

On Sep 17, 3:40 pm, "Bernard Liengme" <blien......stfx.TRUENORTH.ca>
wrote:
> Except in very special circumstances, a cell cannot contain a function that
> refers to it own cell. That is called a circular reference. One can use
> circular reference in a properly design worksheet but they are unusual.
> Further more no formula (UDF or otherwise) cannot <insert> any formula into
> a cell. A formula has the form =FUNCTION_NAME (arguments) and returns a
> value (or several values in the case of an array formula)
> You need to re-think the task.
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPwww.stfx.ca/people/bliengme
> remove caps from email
> "Chris" <chris.ole......gmail.com> wrote in message
> news:1190057224.891199.99520...r29g2000hsg.googlegroups.com...
> > I'm inserting a custom user defined function into a cell.
> > As a parameter to the function is a cell which is the cell in which
> > the function is located
> > i.e.
> > in cell A5 I have the following function
> > =myCustomFunction(A5)
> > Now, I want to insert this function automatically into that cell but
> > the way I have that cell refrenced is in terms of Rows and Columns,
> > that is A5 = Row 5 Column 1
> > Is there a way I can do this
> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
> > somehow' & ")"

chris | Thu, 22 May 2008 04:00:00 GMT |

Here is a short in the dark.
The VBA Help has this example:
Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"

So here is a suggestion (untested):
Cells(xValue, yValue).FormulaR1C1 =_
"=myCustomFunction(R" & xValue & "C" & yValue & ")"

But it still looks like a circular reference
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" <chris.olekas...gmail.com> wrote in message
news:1190061181.345222.253660...k79g2000hse.googlegroups.com...
> Maybe I wasn't clear in my explanation.
> Within VBA I wrote my own function with the following signature
> Function myCustomFunction(myRange As Range) As Integer
> myCustomFunction = 5 ' just for example
> End Function
> Now I had another sub which I called from a button within a worksheet
> that did this
> Sub insertMyCustomFunction(xValue As Integer, yValue As Integer)
> Cells(xValue, yValue) = "=myCustomFunction("& _
> ' Now here is where I'm having the issue, I have an xValue and a
> yValue that I need to convert to a range i.e. A5
> ")"
> End Sub
> Either way, I fixed this by just changing
> myCustomFunction(myRange As Range)
> to
> myCustomFunction(xCoord As Integer, yCoord As Integer)
> and just passing the coordinates through the insert sub
> On Sep 17, 3:40 pm, "Bernard Liengme" <blien......stfx.TRUENORTH.ca>
> wrote:
>> Except in very special circumstances, a cell cannot contain a function
>> that
>> refers to it own cell. That is called a circular reference. One can use
>> circular reference in a properly design worksheet but they are unusual.
>> Further more no formula (UDF or otherwise) cannot <insert> any formula
>> into
>> a cell. A formula has the form =FUNCTION_NAME (arguments) and returns a
>> value (or several values in the case of an array formula)
>> You need to re-think the task.
>> best wishes
>> --
>> Bernard V Liengme
>> Microsoft Excel MVPwww.stfx.ca/people/bliengme
>> remove caps from email
>> "Chris" <chris.ole......gmail.com> wrote in message
>> news:1190057224.891199.99520...r29g2000hsg.googlegroups.com...
>> > I'm inserting a custom user defined function into a cell.
>> > As a parameter to the function is a cell which is the cell in which
>> > the function is located
>> > i.e.
>> > in cell A5 I have the following function
>> > =myCustomFunction(A5)
>> > Now, I want to insert this function automatically into that cell but
>> > the way I have that cell refrenced is in terms of Rows and Columns,
>> > that is A5 = Row 5 Column 1
>> > Is there a way I can do this
>> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
>> > somehow' & ")"
>
>

bernard | Thu, 22 May 2008 04:01:00 GMT |

Alright,

You've convinced me that it is a circular reference. Is there a way
then to find out what cell the function is being ran in? That is
really the only reason why I need the x, coordinates.

In my previous example I would much rather have

Sub myCustomFunction()
Cell_In_Which_MyCustomFunction_Is_In = 5 ' just for example
End Sub

Is there any way to get this info

On Sep 17, 4:58 pm, "Bernard Liengme" <blien......stfx.TRUENORTH.ca>
wrote:
> Here is a short in the dark.
> The VBA Help has this example:
> Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"
> So here is a suggestion (untested):
> Cells(xValue, yValue).FormulaR1C1 =_
> "=myCustomFunction(R" & xValue & "C" & yValue & ")"
> But it still looks like a circular reference
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPwww.stfx.ca/people/bliengme
> remove caps from email
> "Chris" <chris.ole......gmail.com> wrote in message
> news:1190061181.345222.253660...k79g2000hse.googlegroups.com...
> > Maybe I wasn't clear in my explanation.
> > Within VBA I wrote my own function with the following signature
> > Function myCustomFunction(myRange As Range) As Integer
> > myCustomFunction = 5 ' just for example
> > End Function
> > Now I had another sub which I called from a button within a worksheet
> > that did this
> > Sub insertMyCustomFunction(xValue As Integer, yValue As Integer)
> > Cells(xValue, yValue) = "=myCustomFunction("& _
> > ' Now here is where I'm having the issue, I have an xValue and a
> > yValue that I need to convert to a range i.e. A5
> > ")"
> > End Sub
> > Either way, I fixed this by just changing
> > myCustomFunction(myRange As Range)
> > to
> > myCustomFunction(xCoord As Integer, yCoord As Integer)
> > and just passing the coordinates through the insert sub
> > On Sep 17, 3:40 pm, "Bernard Liengme" <blien......stfx.TRUENORTH.ca>
> > wrote:
> >> Except in very special circumstances, a cell cannot contain a function
> >> that
> >> refers to it own cell. That is called a circular reference. One can use
> >> circular reference in a properly design worksheet but they are unusual.
> >> Further more no formula (UDF or otherwise) cannot <insert> any formula
> >> into
> >> a cell. A formula has the form =FUNCTION_NAME (arguments) and returns a
> >> value (or several values in the case of an array formula)
> >> You need to re-think the task.
> >> best wishes
> >> --
> >> Bernard V Liengme
> >> Microsoft Excel MVPwww.stfx.ca/people/bliengme
> >> remove caps from email
> >> "Chris" <chris.ole......gmail.com> wrote in message
> >>news:1190057224.891199.99520...r29g2000hsg.googlegroups.com...
> >> > I'm inserting a custom user defined function into a cell.
> >> > As a parameter to the function is a cell which is the cell in which
> >> > the function is located
> >> > i.e.
> >> > in cell A5 I have the following function
> >> > =myCustomFunction(A5)
> >> > Now, I want to insert this function automatically into that cell but
> >> > the way I have that cell refrenced is in terms of Rows and Columns,
> >> > that is A5 = Row 5 Column 1
> >> > Is there a way I can do this
> >> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
> >> > somehow' & ")"

chris | Thu, 22 May 2008 04:02:00 GMT |

I think Dave has answered this.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Chris" <chris.olekas...gmail.com> wrote in message
news:1190065143.342278.93490...o80g2000hse.googlegroups.com...
> Alright,
> You've convinced me that it is a circular reference. Is there a way
> then to find out what cell the function is being ran in? That is
> really the only reason why I need the x, coordinates.
> In my previous example I would much rather have
> Sub myCustomFunction()
> Cell_In_Which_MyCustomFunction_Is_In = 5 ' just for example
> End Sub
> Is there any way to get this info
> On Sep 17, 4:58 pm, "Bernard Liengme" <blien......stfx.TRUENORTH.ca>
> wrote:
>> Here is a short in the dark.
>> The VBA Help has this example:
>> Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"
>> So here is a suggestion (untested):
>> Cells(xValue, yValue).FormulaR1C1 =_
>> "=myCustomFunction(R" & xValue & "C" & yValue & ")"
>> But it still looks like a circular reference
>> best wishes
>> --
>> Bernard V Liengme
>> Microsoft Excel MVPwww.stfx.ca/people/bliengme
>> remove caps from email
>> "Chris" <chris.ole......gmail.com> wrote in message
>> news:1190061181.345222.253660...k79g2000hse.googlegroups.com...
>> > Maybe I wasn't clear in my explanation.
>> > Within VBA I wrote my own function with the following signature
>> > Function myCustomFunction(myRange As Range) As Integer
>> > myCustomFunction = 5 ' just for example
>> > End Function
>> > Now I had another sub which I called from a button within a worksheet
>> > that did this
>> > Sub insertMyCustomFunction(xValue As Integer, yValue As Integer)
>> > Cells(xValue, yValue) = "=myCustomFunction("& _
>> > ' Now here is where I'm having the issue, I have an xValue and a
>> > yValue that I need to convert to a range i.e. A5
>> > ")"
>> > End Sub
>> > Either way, I fixed this by just changing
>> > myCustomFunction(myRange As Range)
>> > to
>> > myCustomFunction(xCoord As Integer, yCoord As Integer)
>> > and just passing the coordinates through the insert sub
>> > On Sep 17, 3:40 pm, "Bernard Liengme" <blien......stfx.TRUENORTH.ca>
>> > wrote:
>> >> Except in very special circumstances, a cell cannot contain a function
>> >> that
>> >> refers to it own cell. That is called a circular reference. One can
>> >> use
>> >> circular reference in a properly design worksheet but they are
>> >> unusual.
>> >> Further more no formula (UDF or otherwise) cannot <insert> any formula
>> >> into
>> >> a cell. A formula has the form =FUNCTION_NAME (arguments) and returns
>> >> a
>> >> value (or several values in the case of an array formula)
>> >> You need to re-think the task.
>> >> best wishes
>> >> --
>> >> Bernard V Liengme
>> >> Microsoft Excel MVPwww.stfx.ca/people/bliengme
>> >> remove caps from email
>> >> "Chris" <chris.ole......gmail.com> wrote in message
>> >>news:1190057224.891199.99520...r29g2000hsg.googlegroups.com...
>> >> > I'm inserting a custom user defined function into a cell.
>> >> > As a parameter to the function is a cell which is the cell in which
>> >> > the function is located
>> >> > i.e.
>> >> > in cell A5 I have the following function
>> >> > =myCustomFunction(A5)
>> >> > Now, I want to insert this function automatically into that cell but
>> >> > the way I have that cell refrenced is in terms of Rows and Columns,
>> >> > that is A5 = Row 5 Column 1
>> >> > Is there a way I can do this
>> >> > Cells(5,1) = "=myCustomFunction("& 'convert Row 5 Column 1 to A5
>> >> > somehow' & ")"
>

bernard | Thu, 22 May 2008 04:03:00 GMT |

Microsoft Excel Related Links

Microsoft Excel Hot Answers

getting XL to recognize lower from upper case in IF formula
Excel 2002 SP3 Win XP Pro SP2 *Follow-up to: microsoft.public.excel* Hi, I have an IF formula where =IF(LEFT(D3,3)="ABc",(B3*C3)/50,B3*C3) When LEFT(D3,3)="ABC", unfortunately, it seems XL does not see the difference between the lower case "c" in former example and upper case "C" in latter examp...
Getting Values from Closed Books
Hi,I used a function many moons ago that worked, but now I cannot get it to work again and was hoping someone could give me clue why?The Function is short and shown below:Function GetValue2(Path, File, Sheet, ref)Dim Arg As StringArg = "'" & Path & "[" & File & "]" & Sheet & "'!" & Ran...
Getting username of a locked file
How can I get the username of an opened file in VBA, but not the one in Application.UserName...
Getting space between text and right border. " " not working.
I have a =NOW() cell formatted as: yyyy.mm.dd.ddd., hh"h"mm but even when I make it: yyyy.mm.dd.ddd., hh"h"mm" " I don't get any more space between the end of the time and the right-hand border and it's printing too close to the edge. What is the fix for this one, pls? Thank you! :oD...
Getting Excel to only open an xls file if Macros are enabled
Hey guys like the title says i need a way to password secure an xls file but inorder to do this i need macros enabled. any suggestions....

Microsoft Excel New questions

Zero Before Number
Hi,Does anyone know how to get Excel to allow you to have a zero before a number in a cell (i.e. 0198945)?I am trying to enter specific order numbers, but it automatically removes the zero and I couldn't find a way to turn that off.Thanks for any help,Alex...
yet another date format problem
hello, I'm using Excel 2004 in Mac OS X Tiger. I use dates in dd/mm/yy format, and that's how it is set in my international preferences panel (in Tiger) and how i formated the Excel column. but then i type, say, 4/11/07 (to enter the date 4th nov 07) and excel converts it to 11/04/07....
Y Scale on Charts Both Sides of Chart
For the life of me and can't seem to find a way to have the Y scale (or the x-Scale for that matter) show up on the left AND right (Y Scale) or top and bottom (x-Scale) of an x~y scatter chart.Any ideas?Thanks . . . Rick...
XY Scatter Chart
I need some help with an XY Scatter chart. I need to add a data table to the chart. Is there a way to do this? I routinely add the data table to line and bar charts. In this case the data needs to be in the xy scatter format. Thanks...
xvalues and null series
I have two series that I've made a simple line chart of. One is a column of data, the second is all nulls. The X series is consecutive dates. Next, I record into a macro the act of changing the series values and x values. I don't really make a change, I just type what's already th...
xmldata to formatted xml(spreadsheet ML)
Hello, I am trying to convert xml data to formatted XML. I started up with excel 2003 XML file ( which was formatted) and converted it to a xml data. I used to perl to parse the file and now I need to retain the formatting back. One ugly way I can think of is , read formatted XML( spreadsheet ML...
XML Mapping: Creating a "non-repeating schema element" in VS2005
Question regarding XML mapping in Excel. XML Mapping in Excel allows you to map "non-repeating schema elements" to an individual cell while "repeating schema elements" automatically get handled as an Excel "list". I want a table(XML/XSD) exported from a VS2005 dataset to be CELL mappable rather...
XML Limitations
Hello, I'm facing several problems with XML Import/Export in EXCEL 2007 SP1. Please note that my XML data source references an XSD who's available alongside. During XML Import : - Boolean attribute's value are translated to uppercase and localized. This is a problem if the data is...
XML Functionality in Excel Office 2003 Standard
Hi, I would like to be able to use the XML support that is available with Microsoft Office Excel 2003 , however I am having some problems. The install of Excel 2003 at my company seems to be part of the Office Standard package, which this document states doesn't have the required functional...
XML export not working with macro/code
I recorded a macro where I exported a mapped list to an XML file. It worked fine when recording the macro, and if I use the keyboard shortcut I assigned it also works. But if I try to create a button that runs the macro or copy the macro into the Click() event of another button, I get a run ti...

Microsoft Excel Related Categories

Microsoft Excel