Geometric progression in VBA
in [Programming]
|
From: helene and gabor on 9 May 2010 18:07 Hello Tim, I am trying to un derstand your problem. Peter's program gives me a runtime error. What is in F2? Your green figure was calculated from what formula? Thanks Gabor Sebo "Tim" <cashmachine(a)mt2009.com> wrote in message news:01c3ca36-3b38-41be-8ac4-12f327b7c250(a)e2g2000yqn.googlegroups.com... 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: Peter T on 10 May 2010 03:06 Ensure you have two sheets in the active workbook named "Input" and "Aopen". Put a value between 1 to 256 Input!F2, run the code, then look at Aopen!H110 Regards, Peter T "helene and gabor" <gabelene(a)townisp.com> wrote in message news:%23H5QAQ87KHA.1316(a)TK2MSFTNGP02.phx.gbl... > Hello Tim, > > I am trying to un derstand your problem. Peter's program gives me a > runtime error. > What is in F2? > Your green figure was calculated from what formula? > > Thanks > > Gabor Sebo > "Tim" <cashmachine(a)mt2009.com> wrote in message > news:01c3ca36-3b38-41be-8ac4-12f327b7c250(a)e2g2000yqn.googlegroups.com... > 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: helene and gabor on 10 May 2010 06:09 Thanks very much Peter for replying to me. best regards Gabor Sebo ------------------------------------------------------------------------------------------------------------------------------------------------------------------ "Peter T" <peter_t(a)discussions> wrote in message news:ONbnm9A8KHA.3964(a)TK2MSFTNGP05.phx.gbl... > Ensure you have two sheets in the active workbook named "Input" and > "Aopen". Put a value between 1 to 256 Input!F2, run the code, then look at > Aopen!H110 > > Regards, > Peter T > > "helene and gabor" <gabelene(a)townisp.com> wrote in message > news:%23H5QAQ87KHA.1316(a)TK2MSFTNGP02.phx.gbl... >> Hello Tim, >> >> I am trying to un derstand your problem. Peter's program gives me a >> runtime error. >> What is in F2? >> Your green figure was calculated from what formula? >> >> Thanks >> >> Gabor Sebo >> "Tim" <cashmachine(a)mt2009.com> wrote in message >> news:01c3ca36-3b38-41be-8ac4-12f327b7c250(a)e2g2000yqn.googlegroups.com... >> 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 10 May 2010 09:59 Please disregard my reply. I was confused also. I didn't catch he was summing horizontally. I thought it was vertical. When the op is working in Column 8 ("H"), and wants the product of cells to the left, he wrote... 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])" So, being in Column 8, the op is anticipating having a number like 20, or the product of the 20 cells to the left. This gives an address of G110:XER110 due to wrapping. This is 16,366 cells in Excel 2007. ?Range("G110:XER110").Cells.count 16366 The data in the graph example given was symmetrical. That is why I thought it was vertical, vs horizontal. = = = = = = = HTH :>) Dana DeLouis On 5/10/2010 6:09 AM, helene and gabor wrote: > Thanks very much Peter for replying to me. > > best regards > > Gabor Sebo > ------------------------------------------------------------------------------------------------------------------------------------------------------------------ > > "Peter T" <peter_t(a)discussions> wrote in message > news:ONbnm9A8KHA.3964(a)TK2MSFTNGP05.phx.gbl... >> Ensure you have two sheets in the active workbook named "Input" and >> "Aopen". Put a value between 1 to 256 Input!F2, run the code, then >> look at Aopen!H110 >> >> Regards, >> Peter T >> >> "helene and gabor" <gabelene(a)townisp.com> wrote in message >> news:%23H5QAQ87KHA.1316(a)TK2MSFTNGP02.phx.gbl... >>> Hello Tim, >>> >>> I am trying to un derstand your problem. Peter's program gives me a >>> runtime error. >>> What is in F2? >>> Your green figure was calculated from what formula? >>> >>> Thanks >>> >>> Gabor Sebo >>> "Tim" <cashmachine(a)mt2009.com> wrote in message >>> news:01c3ca36-3b38-41be-8ac4-12f327b7c250(a)e2g2000yqn.googlegroups.com... >>> 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: helene and gabor on 11 May 2010 09:08 Hello Peter, Tim gets the products of his entries given on line 110. His results is in H110. This limits the maximum i to : 7. If he wants to calculate products up to i=256 then the row of inputs would have to occupy 256 columns and the result to be entered into the 257th. column? Best Regards Gabor Sebo ----------------------------------------------------------------------------------------------------------------------------------------------------- "Peter T" <peter_t(a)discussions> wrote in message news:ONbnm9A8KHA.3964(a)TK2MSFTNGP05.phx.gbl... > Ensure you have two sheets in the active workbook named "Input" and > "Aopen". Put a value between 1 to 256 Input!F2, run the code, then look at > Aopen!H110 > > Regards, > Peter T >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Workbook Macro Next: extracting data from one workbook to another |