Another Question for VB Pro
Another Question for VB Pro
(OP)
Dear All,
My question this time is a bit harder:)
I am trying to fill a column with a bunch of formula BUT I need the formula to increase by by the product of i as we go down the cells.
From the following simple example you will see that I am trying to fill 11 cells beginning with "I4" downwards. Say R5C5 = 1 and R1C1 =2. The results will be 2 for all the 11 cells. But what I am trying to do is to have each cells to increase by the product of "i". E.g. 0, 2, 4, 6, 8, 10...20.
___________
For i = 0 To 10
Range("I4").Offset(i, 0).FormulaR1C1= _
"=R5C5*R1C1"
Next i
____________
It would be easy if we can write something like
**********
For i = 0 To 10
Range("I4").Offset(i, 0).FormulaR1C1= _
"=R5C5*R1C1*i"
Next i
**********
But I cannot do that... so I am wondering if there is better way to do it?
Thanks in advance!
Regards,
YEN
My question this time is a bit harder:)
I am trying to fill a column with a bunch of formula BUT I need the formula to increase by by the product of i as we go down the cells.
From the following simple example you will see that I am trying to fill 11 cells beginning with "I4" downwards. Say R5C5 = 1 and R1C1 =2. The results will be 2 for all the 11 cells. But what I am trying to do is to have each cells to increase by the product of "i". E.g. 0, 2, 4, 6, 8, 10...20.
___________
For i = 0 To 10
Range("I4").Offset(i, 0).FormulaR1C1= _
"=R5C5*R1C1"
Next i
____________
It would be easy if we can write something like
**********
For i = 0 To 10
Range("I4").Offset(i, 0).FormulaR1C1= _
"=R5C5*R1C1*i"
Next i
**********
But I cannot do that... so I am wondering if there is better way to do it?
Thanks in advance!
Regards,
YEN





RE: Another Question for VB Pro
One easy way to do it in excel would be to type in your first two values, and then select them both and drag-the-plus to fill the remainder.
Another way would be to type in your first value, then write a function for your second one:
ie
R1C1 = 1
R2C2 = ((1/increment)+1)*increment
or alternatively
R2C2 = R1C1 + increment
Did I miss the point completely, or is that what you were trying to accomplish?
RE: Another Question for VB Pro
Thanks for your reply. The example which I gave is gross simplification. The actual formula which is much more complex. Sorry for causing misunderstanding in my question.
Regards,
YEN
RE: Another Question for VB Pro
Another method that I've found useful - construct a series of formulae as text within cells, using text combination functions, etc., then copy everything and paste it into notepad, then copy everything and paste it back into excel. Quite often excel will assume that you mean to be pasting formulae, and it will automatically convert the text to such.
RE: Another Question for VB Pro
Sorry for my late reply as I am away for few weeks.
Having "count" variable (i) in one column and a column next to it is a good idea. However, I am trying to write a visual basic program for excel which helps me fill in the cell with formula. As you know, if I do that, I will have all the cells fill in with the same formula, so for example, if the formula refer to cell A1, then all of the cell which I fill with will refer to A1.
I am trying to find a way out of it....
Regards,
YEN
RE: Another Question for VB Pro
Dim i As Integer
Dim my_formula_string As String
For i = 1 To 10
my_formula_string = "=A" + format(i * 3, "0") + "+5"
Cells(i, 2).Formula = my_formula_string
Next i
RE: Another Question for VB Pro
Regards,
YEN