Obtaining column/row numbers of cells in user defined function

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

- 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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Hi Sébastien,

Now, it makes sense. Thank you very much!