Geometric progression in VBA

T

Tim

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
 
P

Peter T

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
 
T

Tim

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
Click to expand...

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?
 
D

Dana DeLouis

Geometric progression in VBA...
sFmla = "=PRODUCT(RC[-"& i& "]:RC[-1])"
Click to expand...

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



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
Click to expand...

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?
Click to expand...
 
D

Dana DeLouis

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

Geometric progression in VBA...
sFmla = "=PRODUCT(RC[-"& i& "]:RC[-1])"
Click to expand...

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



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
Click to expand...

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?
Click to expand...
Click to expand...
 
H

helene and gabor

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
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
Click to expand...

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?
 
P

Peter T

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 said:
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
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
Click to expand...

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?
Click to expand...
 
H

helene and gabor

Thanks very much Peter for replying to me.

best regards

Gabor Sebo
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Peter T said:
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 said:
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
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
Click to expand...

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?
Click to expand...
Click to expand...
 
D

Dana DeLouis

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


Thanks very much Peter for replying to me.

best regards

Gabor Sebo
------------------------------------------------------------------------------------------------------------------------------------------------------------------

Peter T said:
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 said:
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
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?
Click to expand...
Click to expand...
Click to expand...
 
H

helene and gabor

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
 
P

Peter T

Good catch!

Actually i = 8 to 255 does work (but not 256), eg

i = 8, formula: =PRODUCT(G110:IV110)
i = 255, formula: =PRODUCT(G110:I110)

however these are presumably not valid

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help plz in sorting out the error in problem 2
Help with SUMIF in VBA 1
Excel VBA-use variable in active cell formula problem 9
formula in VBA 1
Hyperlink jpg files in Dir via VBA 1
Need assistance with secondary axis in Excel 2007 0
Run-time error '-2146233079 (80131509)' in the Excel Application 0

Top