×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Iterating through a set values in a sheet

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

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

RE: Iterating through a set values in a sheet

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

RE: Iterating through a set values in a sheet

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

RE: Iterating through a set values in a sheet

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.

RE: Iterating through a set values in a sheet

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.

RE: Iterating through a set values in a sheet

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

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.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources