Geometric progression in VBA
in [Programming]
|
From: Tim on 8 May 2010 04:21 Hello, I am looking use the PRODUCT function in an efficient way by 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
From: Peter T on 8 May 2010 04:52 Sub test2() Dim i As Double Dim sFmla As String Dim cel As Range i = ActiveWorkbook.Worksheets("Input").Range("F2").Value If i >= 1 And i <= 256 Then Set cel = ActiveWorkbook.Worksheets("Aopen").Range("H110") sFmla = "=PRODUCT(RC[-" & i & "]:RC[-1])" cel.Formula = sFmla Debug.Print sFmla Else ' Msgbox i & " is out of range End If End Sub also note, no need to select Regards, Peter T "Tim" <cashmachine(a)mt2009.com> wrote in message news:c6d6cab5-3b28-45a3-88a8-d1c293fa986a(a)n15g2000yqf.googlegroups.com... > Hello, I am looking use the PRODUCT function in an efficient way by > 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
From: Tim on 8 May 2010 06:32 On 8 mai, 10:52, "Peter T" <peter_t(a)discussions> wrote: > Sub test2() > Dim i As Double > Dim sFmla As String > Dim cel As Range > > i = ActiveWorkbook.Worksheets("Input").Range("F2").Value > > If i >= 1 And i <= 256 Then > Set cel = ActiveWorkbook.Worksheets("Aopen").Range("H110") > sFmla = "=PRODUCT(RC[-" & i & "]:RC[-1])" > cel.Formula = sFmla > Debug.Print sFmla > Else > ' Msgbox i & " is out of range > End If > > End Sub > > also note, no need to select > > Regards, > Peter T Thanks, it works perfectly! (see result in green cell). http://cjoint.com/data/fimzCcmNG6.htm To replicate the same formula from row 105 to 114 (instead of 110 only), should i use a for/next? What would you suggest?
From: Dana DeLouis on 8 May 2010 06:53 > Geometric progression in VBA... > sFmla = "=PRODUCT(RC[-"& i& "]:RC[-1])" Hi. Based on the example, maybe... (4 being 5-1 = 4) Sub Demo() Dim R, C C = [H1].Column For R = 105 To 114 Cells(R, C).FormulaR1C1 = "=Fact(RC[-1])/Fact(4)" Next R End Sub = = = = = = = HTH Dana DeLouis On 5/8/2010 6:32 AM, Tim wrote: > On 8 mai, 10:52, "Peter T"<peter_t(a)discussions> wrote: >> Sub test2() >> Dim i As Double >> Dim sFmla As String >> Dim cel As Range >> >> i = ActiveWorkbook.Worksheets("Input").Range("F2").Value >> >> If i>= 1 And i<= 256 Then >> Set cel = ActiveWorkbook.Worksheets("Aopen").Range("H110") >> sFmla = "=PRODUCT(RC[-"& i& "]:RC[-1])" >> cel.Formula = sFmla >> Debug.Print sFmla >> Else >> ' Msgbox i& " is out of range >> End If >> >> End Sub >> >> also note, no need to select >> >> Regards, >> Peter T > > Thanks, it works perfectly! > (see result in green cell). > http://cjoint.com/data/fimzCcmNG6.htm > > To replicate the same formula from row 105 to 114 (instead of 110 > only), should i use a for/next? > What would you suggest? -- = = = = = = = HTH :>) Dana DeLouis
From: Dana DeLouis on 8 May 2010 07:09
Here's an example of your formula. I assume your data is in Column G, and you are putting the equation in Column H. Sub Demo() Dim R, C C = [H1].Column For R = 105 To 114 Cells(R, C).FormulaR1C1 = "=Fact(RC[-1])/Fact(4)" Next R '// Or... For R = 105 To 114 Cells(R, C).FormulaR1C1 = "=Product(R105C[-1]:RC[-1])" Next R End Sub = = = = = HTH :>) Dana DeLouis On 5/8/2010 6:53 AM, Dana DeLouis wrote: > > Geometric progression in VBA... > > sFmla = "=PRODUCT(RC[-"& i& "]:RC[-1])" > > Hi. Based on the example, maybe... > (4 being 5-1 = 4) > > Sub Demo() > Dim R, C > C = [H1].Column > > For R = 105 To 114 > Cells(R, C).FormulaR1C1 = "=Fact(RC[-1])/Fact(4)" > Next R > End Sub > > = = = = = = = > HTH > Dana DeLouis > > > > On 5/8/2010 6:32 AM, Tim wrote: >> On 8 mai, 10:52, "Peter T"<peter_t(a)discussions> wrote: >>> Sub test2() >>> Dim i As Double >>> Dim sFmla As String >>> Dim cel As Range >>> >>> i = ActiveWorkbook.Worksheets("Input").Range("F2").Value >>> >>> If i>= 1 And i<= 256 Then >>> Set cel = ActiveWorkbook.Worksheets("Aopen").Range("H110") >>> sFmla = "=PRODUCT(RC[-"& i& "]:RC[-1])" >>> cel.Formula = sFmla >>> Debug.Print sFmla >>> Else >>> ' Msgbox i& " is out of range >>> End If >>> >>> End Sub >>> >>> also note, no need to select >>> >>> Regards, >>> Peter T >> >> Thanks, it works perfectly! >> (see result in green cell). >> http://cjoint.com/data/fimzCcmNG6.htm >> >> To replicate the same formula from row 105 to 114 (instead of 110 >> only), should i use a for/next? >> What would you suggest? > > -- = = = = = = = HTH :>) Dana DeLouis |