For-next loop with 2 variables

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
How do I get my loop to change the p and i at the same time without nesting?

For i = 1 To Number_of_Periods
For p = 1 To Number_of_Periods
ActiveCell.FormulaR1C1 = "=R7C3/2"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=R8C3/2"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=POWER(1+RC[-2], p)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-5]/RC9"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-5]/RC9"
ActiveCell.Offset(1, -6).Select
Next p, i

Thanks,

Rasberry
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Rasberry

What are you actually trying to do with this code?

You don't even seem to be using the i variable anywhere and the p is in a text string so will probably be regarded as a named range by Excel.
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
Yes, you are correct, when I use "P" it comes back with a name error. I'm trying to insert a power formula in a certain number of lines of excel (depending on number of periods) that is raised to a power each time. In Period 1 (cell 1), the interest rate is raised to the power "1", in period 2 (next cell), the interest rate is raised to the power "2", etc.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
I'm about to go offline but I'll ask this, why do you have a nested loop? You are trying to use 1 variable, p, but you don't seem to be using i at all.

Oh wait a minute I think I get it, your using the Select to move cells and using i to iterate that.

Are you sure you actually need to do that, couldn't you just copy the formula?
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
I'll think about whether I can use the copy option. It works when I only use the "i". However, both the p and i need to be dynamic, I just want them to change at the same time. I'll keep thinking. Thanks for the suggestion about the copy option.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
Rasberry

You say you need the i to be dynamic but I don't see you actually using it in the code.

Maybe I'm missing something?:)
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
Well, if I remove the "p" from the loop and the "p" from the formula and replace it with "1", the loop inserts the formula "i" times, where "i" is defined by Number_of_Periods (in the code prior to the piece I pasted in the message).
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
cool--figured it out

My formula code was wrong. I always make this mistake. This following code works. (To your point that my mistake was causing the formula to be a string and the "i" was hardcoded as an i.)

For I = 1 To Number_of_Periods - 1
ActiveCell.FormulaR1C1 = "=R7C3/2"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=R8C3/2"
ActiveCell.Offset(0, 2).Select
ActiveCell.FormulaR1C1 = "=POWER(1+RC[-2], " & I + 1 & " )"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-5]/RC9"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-5]/RC9"
ActiveCell.Offset(1, -6).Select
Next I
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,288
When you run the code what's the activecell?
 

Rasberry

Board Regular
Joined
Aug 11, 2002
Messages
158
The active cell is defined in the lines of code before this section of code. As the code goes through the loop, it inserts the formulas on the lines as instructed by the offset coding.
 

Forum statistics

Threads
1,078,472
Messages
5,340,542
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top