Prev: Workbook Macro
Next: extracting data from one workbook to another
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