×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

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

Regards,
YEN

### RE: Another Question for VB Pro

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?

### RE: Another Question for VB Pro

(OP)
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

### RE: Another Question for VB Pro

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.

### RE: Another Question for VB Pro

(OP)
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

### RE: Another Question for VB Pro

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") + "+5"
Cells(i, 2).Formula = my_formula_string
Next i

### RE: Another Question for VB Pro

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

Regards,
YEN

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

#### Resources

White Paper - How ESI is Helping Move New Medical Device Product to Market Quicker & More Cost Effic
Early Supplier Involvement has long been a strategy employed by manufacturers to produce innovative products. Now, it almost seems like a necessity. Because decisions made in the design phase can positively affect product quality and costs, this can help add value to OEM bottom lines. This white paper will discuss many facets of ESI, including why itâ€™s so valuable today, what challenges limit the benefits of ESI, how cost is impacted, and more. Download Now

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!