Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# macro in excel

Posted on 2016-09-01
Medium Priority
60 Views
Hi
I m trying to compute simulate 500 lines, each line cosists of 260 random points( rand () ) So what I wanna do is generate a graph with 500 paths by using macro in Excel. Can you briefly show me how it works?

Regards
Sijin
0
Question by:sijin wu
• 2

LVL 18

Accepted Solution

xtermie earned 1600 total points (awarded by participants)
ID: 41779965
You can use this macro to create the random numbers
``````Sub randomnums()
Dim nocols, norows As Integer
Dim ws As Worksheet

norows = 200    'you can change these
nocols = 500    'you can change these
Set ws = Application.ActiveSheet

Application.ScreenUpdating = False

For i = 1 To norows
For j = 1 To nocols
ws.Cells(i, j).Activate
ws.Cells(i, j).Formula = "=Rand()"
Next j
Next i

Application.ScreenUpdate = True
End Sub
``````
In your Excel file you can have the graph set up to use the random range.
If you want we can write a separate macro (or append the above) to create the line graph
1

LVL 37

Assisted Solution

Kimputer earned 400 total points (awarded by participants)
ID: 41780019
Above code obviously works, but please note the following:

- Excel usually only allows 255 lines, not the 500 you want
- Displaying random numbers, (graph/line/bars or otherwise) is usually of NO USE. IT will be ONE BIG BLOCK OF BLACK DOTS (if not, the random generator is defect). Or in Excel, it's grey, since all the colors fade into each other (lines visiable for around 15 lines, above 50, it's getting like a full block of grey).
0

LVL 18

Expert Comment

ID: 41808387
Good comments, code and recommendations provided
0

## Featured Post

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
New style of hardware planning for Microsoft Exchange server.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on tâ€¦
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculâ€¦
###### Suggested Courses
Course of the Month11 days, left to enroll