## 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