Iterating through a set values in a sheet

[JasonNicholson]

I have a sheet that calculates PV given the variable i. However, I need to run through many values of i. I need a marco that runs through a loop for given set of i values and calculates PV. Does any have suggestions on how to do this?

-Jason Nicholson

 

[palusa]

Not sure to have well understood...
However, supposing your Is are in the range B3:B7, try this to loop and compute PV(i) and print nearby the Is.

----------

Public Sub iLoop()
Dim iRange As Range, Rg0 As Range, PV
Set iRange = [B3:B7]
For Each cell In iRange ' Calculates PVs with current i value
Set Rg0 = Range(cell.Address)
i = cell
Count = WorksheetFunction.Match(i, iRange, 0)

For j% = 1 To 10 ' here start your PV(i) calculation
PV = i ^ 2 + 2 * i + 1 '
Rg0.Offset(j% - Count, Count) = PV ' and stores in a cell
Next

Next
End Sub

------

HTH

LF

 

[Clyde38]

Are you sure that you need a macro? You can do the same thing by creating a table.

 

[JasonNicholson]

Clyde38,

The way my sheet is setup is most of it is a large table that is used to calculate PV. i is one variable used in the calculation of PV. Basically if wanted to do calculate PV for different i's manually I could paste a new i copy the PV and paste it somewhere to be stored for later. This tedious since I have several hundered i values to calculate PV.

-Jason Nicholson

 

[cummings54]

You can use the Data:Table menu item to create a table of PV versus "i".

Just select an unused part of the worksheet that has the PV calculation in it. Then make a formula that references the PV result. Now put the i values of interest in a column one cell down and one cell left of the reference formula. Select the reference formula and the column of "i"s in one rectangular selection. Use the Data:Table menu item to activate the table dialog and enter the cell address that you input the "i". You should see a table with i on the left and pv on the right.

 

[Clyde38]

Thanx cummings54. I should have been more explicit (Create a one-variable data table).
Jason,
If you need more help just use the help feature for "Create a one-variable data table".
This is really easy, and if you already have a column or row if "i's" you are almost done.

 

[JasonNicholson]

Cummings54 and Clyde38,

The Data: Table menu item did the trick. I did write a macro that did the same thing but it was extremely slow because it involved cutting and pasting hundreds of cells in a for loop (as you can probably tell I am newbie with writing macros).

-Jason Nicholson