Obtaining column/row numbers of cells in user defined function

Hi,

I am writing a simple function which will use two cell values in Range("A1")
and Range("D5"). I would like to define the difference between them as a
distance in terms of columns and row numbers. I was wondering how I can get
the row/column numbers of two range parameters in a user defined function,
i.e., Function (A1, A2)

Thanks.

Hi

1 .Using existing ROW( ) and COLUMN( ), it would be:
= Row(D5) - ROW(A1)
and
=COLUMN(D5)-COLUMN(A1)

2. Using a vba user defined func, it would be

Function RowDiff(Rg1 As Range, Rg2 As Range) As Long
RowDiff = Rg2.Cells(1).Row - Rg1.Cells(1).Row
End Function

and

Function ColDiff(Rg1 As Range, Rg2 As Range) As Long
ColDiff = Rg2.Cells(1).Column - Rg1.Cells(1).Column
End Function

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"GreenInIowa" wrote:

> Hi,
>
> I am writing a simple function which will use two cell values in Range("A1")
> and Range("D5"). I would like to define the difference between them as a
> distance in terms of columns and row numbers. I was wondering how I can get
> the row/column numbers of two range parameters in a user defined function,
> i.e., Function (A1, A2)
>
> Thanks.

Hi Sebastien,

Thanks for the tip. I modified the function as you showed, but when I used
the "distance" with exponent the result is not correct. I was wondering if
"^" is not working as exponent in my computer. Thanks.



Function CumGrowth(SecondNumber As Range, FirstNumber As Range) As Long

distance = SecondNumber.Cells(1).Row - FirstNumber.Cells(1).Row
CumGrowth = ((SecondNumber / FirstNumber)) ^ (1 / distance)

End Function

"sebastienm" wrote:

> Hi
>
> 1 .Using existing ROW( ) and COLUMN( ), it would be:
> = Row(D5) - ROW(A1)
> and
> =COLUMN(D5)-COLUMN(A1)
>
> 2. Using a vba user defined func, it would be
>
> Function RowDiff(Rg1 As Range, Rg2 As Range) As Long
> RowDiff = Rg2.Cells(1).Row - Rg1.Cells(1).Row
> End Function
>
> and
>
> Function ColDiff(Rg1 As Range, Rg2 As Range) As Long
> ColDiff = Rg2.Cells(1).Column - Rg1.Cells(1).Column
> End Function
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "GreenInIowa" wrote:
>
> > Hi,
> >
> > I am writing a simple function which will use two cell values in Range("A1")
> > and Range("D5"). I would like to define the difference between them as a
> > distance in terms of columns and row numbers. I was wondering how I can get
> > the row/column numbers of two range parameters in a user defined function,
> > i.e., Function (A1, A2)
> >
> > Thanks.

- i guess your result could be now a Double and not a Long
- Also have you tried inserting some 'debug.print' lines for testing
- Could you please give a few examples of non-correct results.

Rewrite write result datatype change and debug.print. Also i have changed
SecondNumber to SecondNumber.Cells(1) to make sure a single value was
returned and not an array. Same with FirstNumber:

Function CumGrowth( _
SecondNumber As Range, _
FirstNumber As Range) As Double

Dim distance As Double
distance = SecondNumber.Cells(1).Row - FirstNumber.Cells(1).Row
CumGrowth = ((SecondNumber.Cells(1) / FirstNumber.Cells(1))) ^ (1 /
distance)
Debug.Print SecondNumber, FirstNumber, distance

End Function

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"GreenInIowa" wrote:

> Hi Sebastien,
>
> Thanks for the tip. I modified the function as you showed, but when I used
> the "distance" with exponent the result is not correct. I was wondering if
> "^" is not working as exponent in my computer. Thanks.
>
>
>
> Function CumGrowth(SecondNumber As Range, FirstNumber As Range) As Long
>
> distance = SecondNumber.Cells(1).Row - FirstNumber.Cells(1).Row
> CumGrowth = ((SecondNumber / FirstNumber)) ^ (1 / distance)
>
> End Function
>
> "sebastienm" wrote:
>
> > Hi
> >
> > 1 .Using existing ROW( ) and COLUMN( ), it would be:
> > = Row(D5) - ROW(A1)
> > and
> > =COLUMN(D5)-COLUMN(A1)
> >
> > 2. Using a vba user defined func, it would be
> >
> > Function RowDiff(Rg1 As Range, Rg2 As Range) As Long
> > RowDiff = Rg2.Cells(1).Row - Rg1.Cells(1).Row
> > End Function
> >
> > and
> >
> > Function ColDiff(Rg1 As Range, Rg2 As Range) As Long
> > ColDiff = Rg2.Cells(1).Column - Rg1.Cells(1).Column
> > End Function
> >
> > --
> > Regards,
> > Sébastien
> > <http://www.ondemandanalysis.com>
> >
> >
> > "GreenInIowa" wrote:
> >
> > > Hi,
> > >
> > > I am writing a simple function which will use two cell values in Range("A1")
> > > and Range("D5"). I would like to define the difference between them as a
> > > distance in terms of columns and row numbers. I was wondering how I can get
> > > the row/column numbers of two range parameters in a user defined function,
> > > i.e., Function (A1, A2)
> > >
> > > Thanks.

Hi Sébastien,

Thanks for your help. Now, things are working fine. However, I must admit
that I do not quite understand the usage of Cells( ) function only one
parameter, such as Cells(i). When I look at the manual it always defines it
with two parmeters, such as Cells (i,j).

Thanks again.



"sebastienm" wrote:

> - i guess your result could be now a Double and not a Long
> - Also have you tried inserting some 'debug.print' lines for testing
> - Could you please give a few examples of non-correct results.
>
> Rewrite write result datatype change and debug.print. Also i have changed
> SecondNumber to SecondNumber.Cells(1) to make sure a single value was
> returned and not an array. Same with FirstNumber:
>
> Function CumGrowth( _
> SecondNumber As Range, _
> FirstNumber As Range) As Double
>
> Dim distance As Double
> distance = SecondNumber.Cells(1).Row - FirstNumber.Cells(1).Row
> CumGrowth = ((SecondNumber.Cells(1) / FirstNumber.Cells(1))) ^ (1 /
> distance)
> Debug.Print SecondNumber, FirstNumber, distance
>
> End Function
>
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "GreenInIowa" wrote:
>
> > Hi Sebastien,
> >
> > Thanks for the tip. I modified the function as you showed, but when I used
> > the "distance" with exponent the result is not correct. I was wondering if
> > "^" is not working as exponent in my computer. Thanks.
> >
> >
> >
> > Function CumGrowth(SecondNumber As Range, FirstNumber As Range) As Long
> >
> > distance = SecondNumber.Cells(1).Row - FirstNumber.Cells(1).Row
> > CumGrowth = ((SecondNumber / FirstNumber)) ^ (1 / distance)
> >
> > End Function
> >
> > "sebastienm" wrote:
> >
> > > Hi
> > >
> > > 1 .Using existing ROW( ) and COLUMN( ), it would be:
> > > = Row(D5) - ROW(A1)
> > > and
> > > =COLUMN(D5)-COLUMN(A1)
> > >
> > > 2. Using a vba user defined func, it would be
> > >
> > > Function RowDiff(Rg1 As Range, Rg2 As Range) As Long
> > > RowDiff = Rg2.Cells(1).Row - Rg1.Cells(1).Row
> > > End Function
> > >
> > > and
> > >
> > > Function ColDiff(Rg1 As Range, Rg2 As Range) As Long
> > > ColDiff = Rg2.Cells(1).Column - Rg1.Cells(1).Column
> > > End Function
> > >
> > > --
> > > Regards,
> > > Sébastien
> > > <http://www.ondemandanalysis.com>
> > >
> > >
> > > "GreenInIowa" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am writing a simple function which will use two cell values in Range("A1")
> > > > and Range("D5"). I would like to define the difference between them as a
> > > > distance in terms of columns and row numbers. I was wondering how I can get
> > > > the row/column numbers of two range parameters in a user defined function,
> > > > i.e., Function (A1, A2)
> > > >
> > > > Thanks.

Many Properties or methods have multiple interfaces for their parameters
eg: you can call Workbooks(1) or Workbooks("mybook.xls")
Kind of the same idea with Cells
- if two parameters Cells(i,j) returns the cell at row i and column j
within the range
- if only one parameter Cells(i) returns the i th cell in the range
counting from left to right then top to down: eg in A1:C10, it counts in the
following order A1, B1, C1, then next row A2, B2, C2, then next row A3 ....

In the function, i used Cells(1) to make sure that if the parameter passed
(say FirstNumber) is:
- A1 ---> FirstNumber.Cells(1) = A1
- if A1:D10 is passed, then .Cells(1) return the first cell only = A1.
This ensures a single value and therefore no error, else using FirstNumber on
a multi-cell range would return an array of values and would create an error
in the formula.

I hope this helps
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"GreenInIowa" wrote:

> Hi Sébastien,
>
> Thanks for your help. Now, things are working fine. However, I must admit
> that I do not quite understand the usage of Cells( ) function only one
> parameter, such as Cells(i). When I look at the manual it always defines it
> with two parmeters, such as Cells (i,j).
>
> Thanks again.
>

Hi Sébastien,

Now, it makes sense. Thank you very much!



"sebastienm" wrote:

>
> Many Properties or methods have multiple interfaces for their parameters
> eg: you can call Workbooks(1) or Workbooks("mybook.xls")
> Kind of the same idea with Cells
> - if two parameters Cells(i,j) returns the cell at row i and column j
> within the range
> - if only one parameter Cells(i) returns the i th cell in the range
> counting from left to right then top to down: eg in A1:C10, it counts in the
> following order A1, B1, C1, then next row A2, B2, C2, then next row A3 ....
>
> In the function, i used Cells(1) to make sure that if the parameter passed
> (say FirstNumber) is:
> - A1 ---> FirstNumber.Cells(1) = A1
> - if A1:D10 is passed, then .Cells(1) return the first cell only = A1.
> This ensures a single value and therefore no error, else using FirstNumber on
> a multi-cell range would return an array of values and would create an error
> in the formula.
>
> I hope this helps
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "GreenInIowa" wrote:
>
> > Hi Sébastien,
> >
> > Thanks for your help. Now, things are working fine. However, I must admit
> > that I do not quite understand the usage of Cells( ) function only one
> > parameter, such as Cells(i). When I look at the manual it always defines it
> > with two parmeters, such as Cells (i,j).
> >
> > Thanks again.
> >