Solved

Repeat a macro in excel, pasted values update a chart.  Keep chart in view while calculations take place.

Posted on 2015-01-08
6
250 Views
Last Modified: 2015-01-08
I have recorded a macro to calculate a sheet (f9), copy a cell, paste that cell value, then copy a range of cells, paste the cell values.  The copied cell that changes value with each calculation [based upon NORM.INV(RAND)] is pasted in a single column one row lower each time.  The copied range of cells also changes with each calculation and that range is pasted in a columns that move sequentially to the right.  The values in the columns are used to create a line chart in the spread sheet.  I've recorded a macro that does what I want, but I only recorded it for 6 iterations.  I'd like 100 iterations.  Also, I need the view to remain on the chart during the calculations, since watching the line plots real time adds to the impact of the chart.  Currently, the focus of the spreadsheet moves to the area of the pasted values, then returns to the chart area based upon my final cell selection.  The attachment shows only the first three calculate, copy, paste operations.
Recorded-Macro.docx
0
Comment
Question by:ronadair
  • 3
  • 3
6 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40539227
Please try this... Because it isn't using the 'select' method it should leave the focus alone.

Sub iterate100()
Dim startCell As Range, vertColSource As Range, vertColDest As Range
Dim destCol As Integer, colOffset As Integer
Dim destCell As Range
destrow = 13
colOffset = 4
Set startCell = Range("S6")
Set vertColSource = Range("U13:U48")
For ctr = 1 To 100
    Calculate
    startCell.Copy
    Set destCell = Range("X" & destrow)
    'destCell.PasteSpecial Paste:=xlPasteValues
    destCell.Value = startCell.Value
    Set vertColDest = vertColSource.Offset(0, colOffset)
    vertColDest.Value = vertColSource.Value
    destrow = destrow + 1
    colOffset = colOffset + 1
    DoEvents
Next ctr

Open in new window

0
 

Author Comment

by:ronadair
ID: 40539255
SimonAdept - I copied and pasted your macro got the error message

Compile error:

Expected End Sub
0
 
LVL 18

Expert Comment

by:Simon
ID: 40539267
Hi Sorry, I missed that line when I copied it... try again.
Sub iterate100()
Dim startCell As Range, vertColSource As Range, vertColDest As Range
Dim destCol As Integer, colOffset As Integer
Dim destCell As Range
destrow = 13
colOffset = 4
Set startCell = Range("S6")
Set vertColSource = Range("U13:U48")
For ctr = 1 To 100
    Calculate
    Set destCell = Range("X" & destrow)
    destCell.Value = startCell.Value
    Set vertColDest = vertColSource.Offset(0, colOffset)
    vertColDest.Value = vertColSource.Value
    destrow = destrow + 1
    colOffset = colOffset + 1
    DoEvents
Next ctr
End Sub

Open in new window

Iterate-100.xlsm
I've posted the working example workbook too.
0
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 

Author Comment

by:ronadair
ID: 40539318
Excellent!  Works perfectly.  One more question, if I may.  What do I delete from the macro if I need to eliminate the select, copy, paste of the changing values in the cell S6 that get pasted into the X column?  In other words, I only need the changing values copied and pasted from the range of cells U13:U48.
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40539330
You would comment out these two lines (rows 11 & 12 in my code listing above)

    Set destCell = Range("X" & destrow)   'this is an object reference assignment that isn't needed if you're not using it
    destCell.Value = startCell.Value           'this is the line that 'copies' the value from S6 to a single cell in column X

I'm kind of intrigued about what you're using this for. Can you tell us or is it top secret? :)
0
 

Author Closing Comment

by:ronadair
ID: 40539333
Thank you!  Fantastic & very timely!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Meetings to discuss business process can waste time, and often do .  The meeting's dialog can get confusing when participants have different professional perspectives and backgrounds.  A jointly-developed process picture helps wade through the confu…
A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

679 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