Iterating through a set values in a sheet
Iterating through a set values in a sheet
(OP)
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





RE: Iterating through a set values in a sheet
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
RE: Iterating through a set values in a sheet
RE: Iterating through a set values in a sheet
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
RE: Iterating through a set values in a sheet
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.
RE: Iterating through a set values in a sheet
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.
RE: Iterating through a set values in a sheet
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