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

#### mathgirl09

##### New Member
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:
Code:
``````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``````

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Joe4

Welcome to the Board!

VBA is not necessary. You can do this pretty easily with a formula:
Code:
``=MIN(A1,A2)/MAX(A1,A2)``

If you really want VBA, you were almost there:
Code:
``````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

##### New Member
Welcome to the Board!

VBA is not necessary. You can do this pretty easily with a formula:
Code:
``=MIN(A1,A2)/MAX(A1,A2)``

If you really want VBA, you were almost there:
Code:
``````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 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

##### New Member
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

##### New Member
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:

Code:
``````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:
Code:
``    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

##### New Member
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

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

Replies
3
Views
309
Replies
0
Views
181
Replies
3
Views
230
Replies
1
Views
362
Replies
6
Views
138

1,195,944
Messages
6,012,436
Members
441,701
Latest member
vnkendijs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back