VBA: How to create a function to divide the smallest number by the largest within a range

[mathgirl09]

Hi, I would like to know what code I'm supposed to use to be able to create a function by dividing the smallest value by the largest one by selecting 2 cells within the range each time until the last value in the range. For example:

A1	50
A2	48
A3	45
A4	42
A5	40

<tbody>
</tbody>

I want to select the cells from A1 to A5 containing the values as noted (A1=50, ..., A5=40) and I would like to select 2 cells everytime in that range so: selecting A1 and A2 and divide the smallest value by the largest : A2/A1 (= 48/50); then select A2 and A3, and divide A3 by A2 (=45/48) and so on.

I tried:

Function ratio(ByVal nb1 As Single, ByVal nb2 As Single) As Single

If nb2 > nb1 Then
    ratio = nb1 / nb2
Else
    Choose nb2 > nb1


End If
End Function

 

[Joe4]

Welcome to the Board!

VBA is not necessary. You can do this pretty easily with a formula:

=MIN(A1,A2)/MAX(A1,A2)

If you really want VBA, you were almost there:

Function ratio(ByVal nb1 As Single, ByVal nb2 As Single) As Single

    If nb2 > nb1 Then
        ratio = nb1 / nb2
    Else
        ratio = nb2 / nb1
    End If

End Function

 

[mathgirl09]

Welcome to the Board!

VBA is not necessary. You can do this pretty easily with a formula:

=MIN(A1,A2)/MAX(A1,A2)

If you really want VBA, you were almost there:

Function ratio(ByVal nb1 As Single, ByVal nb2 As Single) As Single

    If nb2 > nb1 Then
        ratio = nb1 / nb2
    Else
        ratio = nb2 / nb1
    End If

End Function

Thank you! But is there a way to create a new Sub and use FormulaR1C1 to plug that function into another row (from B1 to B5)

 

[Joe4]

I am not sure I follow what you are asking.
I thought you want this to work on any two cells you select.
Are you saying that you want to pre-populate a certain range (B1:B5) with formulas?
If so, what is the logic for which cells you want to check in each of those 5 cells?

 

[mathgirl09]

Let's say I'm trying to calculate a certain probability and the ratio is the function I've just created, instead of writing the function "ratio" in each cells, I want to code it instead by using FormulaR1C1 with this new function in VBA. Is there a way to select the range in cells A1 through A5 and code it to get my answer written in cells B1 through B5 by using my new function (ratio) that way?

 

[Joe4]

I don't think we are on the same wavelength here. I am pretty sure we can create a macro to do whatever you want to do, I am just not entirely certain what that is yet!

Is there some sort of correlation between the cells B1:B5 and the cells used in each calculation? If so, what is that rule?
I mean, in this example you are talking about.
What exact cells should be used to calculate the value put in B1?
What exact cells should be used to calculate the value put in B2?
etc.

 

[mathgirl09]

In B1 it should be =A2/A1, B2=A3/A2, B3=A4/A3 and so on...

I just want to plug in my function "ratio" with VBA by using R1C1, for example I tried:

Sub probability()
    Dim rng As Range
    Dim nb As Integer, i As Integer
       
    Sheets("Sheet1").Activate
    
    Set rng = Range("A1").CurrentRegion
    nb = rng.Rows.Count
        
    For i = 1 To nb
        zoneCourante.Cells(i, 2).FormulaR1C1 = "=ratio(RC[0], RC[-1])"


    Next
End Sub

However it always gives me an error message.

 

[Joe4]

A simple line like:

    Range("B1:B5").FormulaR1C1 = "=ratio(RC[-1],R[1]C[-1])"

should do it (you can actually get that VBA code for the formula by using the macro recorder and record yourself entering in the first formula manually).

But what exactly is "zoneCourante"?
Are you trying to place these formulas on a different sheet or something?

 

[mathgirl09]

Oh sorry, it was suppose to be "rng" not zoneCourante. And yes, I have values of A1 to A5 in "Sheet0" but I want to copy the values from "Sheet0" to "Sheet1" and use my "ratio" function in "Sheet1".

 

[mathgirl09]

Thank you so much! I changed the code with the line you gave me and it worked!