Another Question for VB Pro 1

[yliew]

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&quot.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&quot.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

 

[ivymike]

isn't this something that you'd typically do with worksheet functions?

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?

 

[yliew]

Dear IvyMike,

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

 

[ivymike]

Well how about having one column for your "count" variable (i), and a column next to it for your calculation?


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.

 

[yliew]

Thanks Ivymike,

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

 

[ivymike]

something like this, then? (I'm typing it directly in here, instead of testing it first in excel, so let me know if it doesn't work)

Dim i As Integer
Dim my_formula_string As String
For i = 1 To 10
my_formula_string = "=A" + format(i * 3, "0&quot + "+5"
Cells(i, 2).Formula = my_formula_string
Next i

 

[yliew]

Many thanks IvyMike!!!! That's exactly what I need. Thank you very much ... really appreciate your help!

Regards,
YEN