+ Reply to Thread
Results 1 to 5 of 5

Macro to perform a task, write the results, perform the task, write the results . . .

  1. #1
    Registered User
    Join Date
    01-23-2011
    Location
    Carlisle, PA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Macro to perform a task, write the results, perform the task, write the results . . .

    I've used excel to build a rule-based simulation of organizational behavior. Random numbers, generated by excel's "Rand()" function, underlie the whole thing. Therefore, every time I run the simulation, it produces different results (as it should). There are several-hundred million possible values that could result from the simulation, so my goal is to run the thing 1,000 times, in order to get a good feel for the total range of the outputs possible within my model. I would like to find a way to automate this, so that I don't have to copy and paste values from the output cells 1,000 times manually. Indeed, I have five other versions of the model, each of which I need to run 1,000 times (possibly more), so I really need a solution that I can easily apply to different sheets. This seems like it should be easy enough to accomplish using some type of loop, but by inexperience with VBA has prevented me from determining a solution. Can anyone help?

    I can provide more detail if my question is too vague. Thanks in advance for solving my problem.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Macro to perform a task, write the results, perform the task, write the results . . .

    lukegerd,

    Something like this? Make sure the active sheet is the one that contains the result cell. Also change the arrResults(i) = Range("A1").value to be the cell reference you need.
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    01-23-2011
    Location
    Carlisle, PA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to perform a task, write the results, perform the task, write the results . . .

    Tigeravatar,
    I think that your solution is close, but I'm still having some problems. My simulator produces output in a range of cells (B91:AD91). I've tried replacing the red "A1" in your original macro w/ this range, but the resulting output doesn't make much sense. How can I adapt what you've already written in order to handle a range of cells? Thanks for your help!

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Macro to perform a task, write the results, perform the task, write the results . . .

    Updated code:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-23-2011
    Location
    Carlisle, PA
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to perform a task, write the results, perform the task, write the results . . .

    This works perfectly. You're a rock star. Thanks for the help!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1