Actuarial Outpost Simple VBA problem (but not for me)
 Register Blogs Wiki FAQ Calendar Search Today's Posts Mark Forums Read
 FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

Meet Marianne Westphal, DW Simpson Senior Director

#1
10-23-2006, 03:04 PM
 billy idol Member SOA Join Date: Apr 2003 Posts: 90
Simple VBA problem (but not for me)

Please could someone help with a simple macro.

I have a sheet (call it "Calcs") and in cells a1..c1 I have formulae that feed off a number of other formulae and random numbers elsewhere in the same sheet. Instead of pressing F9 repeatedly and copying and pasting (special)each simulation, there must be an easy way. Let's say I want to copy this to another sheet "Results".

So what I want is:

For a = 1 To 1000
Calculate
Copy a1..c1 in Calcs to cell a1..c1 in Results
Calculate
Copy a1..c1 in Calcs to cell a2..c2 in Results
etc...
Calculate
Copy a1..c1 in Calcs to cell a1000..c1000 in Results.
End

Thanks
#2
10-23-2006, 03:09 PM
 NoName Site Supporter Site Supporter Join Date: Nov 2001 Posts: 5,815

Code:
```Sub test()
Dim src As Worksheet, dest As Worksheet
Set src = Worksheets("calcs")
Set dest = Worksheets("results")
For a = 1 To 1000
Application.Calculate
dest.Cells(a, 1).value = src.Cells(1, 1).value
dest.Cells(a, 2).value = src.Cells(1, 2).value
dest.Cells(a, 3).value = src.Cells(1, 3).value
Next
End Sub```
You can replace the 3 "dest=src" lines with a copy, but I prefer to avoid copies if reasonable so I can copy and paste other things while the macro is running.
#3
10-23-2006, 03:42 PM
 billy idol Member SOA Join Date: Apr 2003 Posts: 90

Thank you.
#4
10-23-2006, 03:43 PM
 KRWarner Member Join Date: Oct 2005 Posts: 76

Quote:
 Originally Posted by NoName Code: ```Sub test() Dim src As Worksheet, dest As Worksheet Set src = Worksheets("calcs") Set dest = Worksheets("results") For a = 1 To 1000 Application.Calculate dest.Cells(a, 1).value = src.Cells(1, 1).value dest.Cells(a, 2).value = src.Cells(1, 2).value dest.Cells(a, 3).value = src.Cells(1, 3).value Next End Sub``` You can replace the 3 "dest=src" lines with a copy, but I prefer to avoid copies if reasonable so I can copy and paste other things while the macro is running.
I would also add after the dim line.
Code:
`Application.Calculation = xlCalculationManual`
This will make sure the calculation mode is turned off. I haven't tested the value commands shown, but if you copy paste each cell and don't have calculations turned off the B1 and C1 values will be form differnt Random Number generations.
__________________
I'm not an actuary, but I play one on TV.
#5
10-23-2006, 08:34 PM
 mark007 Member Join Date: Sep 2006 Location: Leeds, UK Favorite beer: Stella Artois Posts: 141

To add to this you should probably store the initial calculation mode and reset it to ensure users aren't annoyed by the change in calc mode. It will also run faster if you do the following:

1. Store the values in an array and output all at once
2. Turn off screen updating.

Finally, I've added a progress message in the status bar.

Code:
```Sub test()

Dim src As Worksheet, dest As Worksheet
Dim OldCalc As XlCalculation
Dim Iterations As Long
Dim i As Long
Dim Results() As Double

OldCalc = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True

'set iterations
Iterations = 1000
ReDim Results(1 To Iterations, 1 To 3)

Set src = Worksheets("calcs")
Set dest = Worksheets("results")

For i = 1 To Iterations
Application.Calculate
Results(i, 1) = src.Cells(1, 1).Value
Results(i, 2) = src.Cells(1, 2).Value
Results(i, 3) = src.Cells(1, 3).Value
Application.StatusBar = Format(i / Iterations, "0.00%") & " complete..."
Next

dest.Range("a1").Resize(Iterations, 3).Value = Results

Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = OldCalc
End Sub```
__________________
Mark Rowlinson
www.thecodenet.com

- Founding Member

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is Off

All times are GMT -4. The time now is 01:08 AM.

 -- Default Style - Fluid Width ---- Default Style - Fixed Width ---- Old Default Style ---- Easy on the eyes ---- Smooth Darkness ---- Chestnut ---- Apple-ish Style ---- If Apples were blue ---- If Apples were green ---- If Apples were purple ---- Halloween 2007 ---- B&W ---- Halloween ---- AO Christmas Theme ---- Turkey Day Theme ---- AO 2007 beta ---- 4th Of July Contact Us - Actuarial Outpost - Archive - Privacy Statement - Top