T

#### Tim

working with range rather than cells (because the number of cells

changes often). The code below is repetitive, do you see better way to

perform this calculation?

In our case, i goes from 1 to 256.

Sub test()

i = Sheets("Input").Range("F2").Value

Sheets("Aopen").Range("H110").Select

If i = 2 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-2]:RC[-1])"

If i = 3 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-3]:RC[-1])"

If i = 4 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-4]:RC[-1])"

If i = 5 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-5]:RC[-1])"

If i = 6 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-6]:RC[-1])"

If i = 7 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-7]:RC[-1])"

If i = 8 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-8]:RC[-1])"

If i = 9 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-9]:RC[-1])"

If i = 10 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-10]:RC[-1])"

If i = 11 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-11]:RC[-1])"

If i = 12 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-12]:RC[-1])"

If i = 13 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-13]:RC[-1])"

If i = 14 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-14]:RC[-1])"

If i = 15 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-15]:RC[-1])"

If i = 16 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-16]:RC[-1])"

If i = 17 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-17]:RC[-1])"

If i = 18 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-18]:RC[-1])"

If i = 19 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-19]:RC[-1])"

If i = 20 Then ActiveCell.FormulaR1C1 = "=PRODUCT(RC[-20]:RC[-1])"

......

End Sub

The guide on Product function

************************************************

Function Product(Arg1, [Arg2], [Arg3], [Arg4], [Arg5], [Arg6], [Arg7],

[Arg8], [Arg9], [Arg10], [Arg11], [Arg12], [Arg13], [Arg14], [Arg15],

[Arg16], [Arg17], [Arg18], [Arg19], [Arg20], [Arg21], [Arg22],

[Arg23], [Arg24], [Arg25], [Arg26], [Arg27], [Arg28], [Arg29],

[Arg30]) As Double

Membre de Excel.WorksheetFunction