I am writing a User Defined Function (UDF) which take some cells as argument. These cells contain the same data but with a different level of precision; the function show the best precision available.
The argument of the funcion are written in the order of ascending precision.
This is an example:
+---+-----------------+---------------------+
| | A | B |
+---+-----------------+---------------------+
| 1 | Best | =get_best(B5;B4;B3) |
| 2 | | |
| 3 | provisional | 1 |
| 4 | definitive | 2 |
| 5 | etched in stone | 12 |
+---+-----------------+---------------------+
The function show 12, because information in cell B5 has a better value than the B4 and B3. For this reason B5 is written before B4 and B3 in the formula argoument.
The code of my UDF is the following:
Public Function get_best(r1 As Range, r2 As Range, r3 As Range) As Variant
get_best = ""
If r3.Value <> "" Then get_best = r3.Value Else
If r2.Value <> "" Then get_best = r2.Value Else
If r1.Value <> "" Then get_best = r1.Value
End Function
It works! but I would like to edit it so it could takes infinite agoument like =get_best(B7;B6;B5;B4;B3).
How could I do that?
Useful comment: "cell B5 has a better value than the B4 and B3" means, for example, that in B3 you have the predicted value you calculated 12 months ago. In cell B5 you have the effective and measured value. So when you have B5 you don't need B3 anymore because "B5 is better than B3"
If the best value is always at the bottom of a Range but you are not sure of the number of the rows in the column you are searching you can use this:
Public Function get_best(rng As Range) As Variant
Dim lngLastRow As Long
lngLastRow = rng.Parent.Cells(rng.Parent.Rows.Count, rng.Column).End(xlUp).Row
get_best = rng.Parent.Cells(lngLastRow, rng.Column).Value
End Function