Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Random Generator

Posted on 2013-05-12
4
Medium Priority
?
315 Views
Last Modified: 2013-05-12
EE Pros,

I am trying to build a small, simple macro that randomly generates responses into a very specific set of cells when you push a button.

I have two columns separated by 3 categories, with 5 cells representing answers to each category; populated from two different Range lists (ListCurrent, ListFuture).

There are 3 categories that separate 5 rows which are Questions where the random results are "the Answers (Answer1 Answer2).

I've attached a sample for you to see what I'm trying to accomplish.  Please add some comments so I know how to scale it.

Thank you in advance,

B.
Random-Generator.xlsm
0
Comment
Question by:Bright01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 40

Expert Comment

by:als315
ID: 39159839
You can enter this formula to your cells (Column C):
=INDIRECT("J" & RANDBETWEEN(49,54),TRUE)
and
=INDIRECT("K" & RANDBETWEEN(49,54),TRUE)
and values will be changed every time when your worksheet will be realculated (you can also press F9)
0
 

Author Comment

by:Bright01
ID: 39159958
Als325, unfortunately a formula won't work (need a macro).  You see, the random generator is to demonstrate what the model looks like after it is populated.  I have to keep the cells clear because new data is going to be entered directly into the cells after the model is cleared (with a .clear command.  

Make sense?

B.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39160363
Here's a macro that puts random responses in a specified range of cells. As written, the code runs on the active worksheet. The statements with comments may require tweaks to suit your actual workbook.
Sub RandomResponse()
Dim v As Variant, vNames As Variant
Dim i As Long, j As Long, n As Long, nResponses As Long
Dim rg As Range, rgResponses As Range, rw As Range
Application.ScreenUpdating = False
Set rg = ActiveSheet.Range("C49:C66")           'First column of random responses go here. Additional responses in successive columns.
vNames = Array("ListCurrent", "ListFuture")     'Names of ranges containing possible responses
For Each v In vNames
    j = j + 1
    i = 0
    Set rgResponses = ActiveWorkbook.Names(v).RefersToRange
    nResponses = rgResponses.Cells.Count
    For Each rw In rg.Rows
        i = i + 1
        If (i Mod 6) <> 1 Then      'Need some means to avoid putting values in row with Category name
            rw.Cells(1, j).Value = rgResponses.Cells(Int(Rnd() * nResponses + 1))
        End If
    Next
Next
End Sub

Open in new window

Random-GeneratorQ28125659.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39160524
Byundt,

Perfect!  Just what I described.  And.....looks like it will scale for me.

All the best,

B.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question