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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]A2[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]42[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]

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]

OK. I recommend that you use the Macro Recorder, and record yourself manually entering the first ratio formula on Sheet1, then stopping the recorder and view the code it just recorded. You should then be able to copy the formula part of that to your code to get it to reference the sheet and range correctly.

It will be a good exercise for you. The Macro Recorder is a very helpful tool when creating VBA code.

 

[mathgirl09]

Alright! Thank you very much!!!