Solved

macro in excel

Posted on 2016-09-01
3
53 Views
Last Modified: 2016-09-21
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?
thats a graph with 100paths
Regards
Sijin
0
Comment
Question by:sijin wu
[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
3 Comments
 
LVL 18

Accepted Solution

by:
xtermie earned 400 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

Open in new window

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 36

Assisted Solution

by:Kimputer
Kimputer earned 100 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

by:xtermie
ID: 41808387
Good comments, code and recommendations provided
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

632 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