Value Paste Macro efficiency

[xluser2007]

Hi All,

I have the following simple sample workbook as attached.

It has just one sheet and a bunch of formulas from B1:I1.

I want to just write a quick macro to value paste this row 1000 below.

I have tried 3 simple methods as follows:

[VBA]Option Explicit

Sub test()

Dim start_time As Double
Dim i As Long

start_time = Timer

Application.ScreenUpdating = False

For i = 1 To 1000

With Range("B1:I1")

.Offset(i, 0).Value = .Value

End With

Next i

Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.ScreenUpdating = True

End Sub

Sub test2()

Dim start_time As Double
Dim i As Long

start_time = Timer

Application.ScreenUpdating = False

For i = 1 To 1000

With Range("B1:I1")

.Copy .Offset(i, 0)

End With

Next i

Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.ScreenUpdating = True

End Sub

Sub test3()

Dim start_time As Double
Dim i As Long

start_time = Timer

Application.ScreenUpdating = False

For i = 1 To 1000

Range("B1:I1").Offset(i, 0) = Application.Evaluate(Range("B1:I1").Value)

Next i

Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.ScreenUpdating = True

End Sub[/VBA]

Sub test3 currently does not work. Here I am trying to evaluate the range formulas as values before value-pasting down 1000 times.

I know this example seems trivial, but it is part of a larger problem of the same process.

If anyone could advise on how to correct method 3 that would be great.

regards,

[xluser2007]

Forgot to add in test2, it doesn't pastevalues, so I tried to change the paste line to the following:

[VBA].Copy .Offset(i, 0).PasteSpecial Paste:=xlPasteValues[/VBA]

But it highlighted the Paste in red as a "syntax error".

How do I paste this as values?

regards,

[Bob Phillips]

[vba]

Sub test4()

Dim start_time As Double
Dim i As Long

start_time = Timer

Application.ScreenUpdating = False

Range("B1:I1").Copy Range("B2:I2")
Range("B1:I2").AutoFill Range("B1:I1").Resize(1000)

Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.ScreenUpdating = True

End Sub
[/vba]

[xluser2007]

Bob, slick use of the autofill. It's cool to see how using Excel's in built functionality can speed up processes amazingly.

The following example is a slight variant of the workbook posted.

Here the yellow cells change value based on another cell A1.

By changing A1 1000 times, we want to recalculate the yellow cells and paste-values as efficiently down for 100 rows as possible.

Here is a simple sample workbook and here is the code I have currently.

[vba]
Option Explicit
Sub test5()
Dim start_time As Double
Dim i As Long
start_time = Timer

Application.ScreenUpdating = False

For i = 1 To 1000

Range("A1").Value = i
With Range("B1:I1")

.Calculate ' Only calculate the relevant range which we want to paste
.Offset(i, 0).Value = .Value
End With
Next i
Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.ScreenUpdating = True
End Sub
[/vba]

Is there a quicker way to do this given that we are changing A1, one at a time, 1000 times?

(BTW, this is pretty close to how my workbook is set up at work, though the calculations take 3 rows to derive the current yellow highlighted cells). I am just after the principle of maximising eficiency and robustness in coding the value-pasting process in this thread, which you have kindly showed me in the above examples)

Also, could you please explain why test2 and the test3 (Evaluate) failed? I would have though Evaluate was the perfect contender for this job, but can't get to work.

Thanks for your help.

[IkEcht]

Try changing PASTE to Datatype

not tested, but should work I guess.

[xluser2007]

Try changing PASTE to Datatype

not tested, but should work I guess.

Hi did you mean change the line to:

[vba]
.Copy .Offset(i, 0).PasteSpecial Datatype:=xlPasteValues
[/vba]

Because this is showing in red with the error:

"Compile Error" Expecting the End of Statement.

Please let me know if I am not following your suggestion as stated or any alternatives.

thanks and regards

[Bob Phillips]

I am not seeing your objective. Yoy load A1, theb process B1:I1 down, these will all be empty.

What do you want to arrive at?

[xluser2007]

I am not seeing your objective. Yoy load A1, theb process B1:I1 down, these will all be empty.

What do you want to arrive at?

Sure,

Suppose the value in A1 is the interest rate in a financial model. And B1:I2 are 8 annual payment projections that depend in the ineterst rate i.e. the A1 value.

I want to conduct scenarios, whereby, to minimise workbook size and overhead, I simply change A1 1000 times, calculate the recalculate the B1:I2 values everytime A1 changes and paste the results as values below.

Thereby we have 1000 scenario values pasted as values (not formulas), as required for analysis.

Is this what you were asking for Bob?

[xluser2007]

BTW, Bob and anyone else good enough to reply to this thread, FYI, I am heading to bed here (quite late), but will reply back first thing tomorrow.

Thanks,

[Bob Phillips]

What is wrong with Test4 that I gave you earlier, or

[vba]

Sub test5()
Dim start_time As Double
Dim i As Long
start_time = Timer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B1:I1").Copy Range("B1:I1000")
Range("B2:I1000").Value = Range("B2:I1000").Value

Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
[/vba]

[xluser2007]

What is wrong with Test4 that I gave you earlier, or

[vba]

Sub test5()
Dim start_time As Double
Dim i As Long
start_time = Timer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Range("B1:I1").Copy Range("B1:I1000")
Range("B2:I1000").Value = Range("B2:I1000").Value

Call MsgBox("Macro took " & (Timer - start_time) & " seconds to run.", vbExclamation Or vbSystemModal, "Time taken for Macro")

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
[/vba]

Bob, your Test4 was a fantastic solution, as is the above, way faster than my looping attempts .

The only thing is, with the revised scenario analysis, where we push through 1000 different values for "A1" and then copy the 1000 results down, the above doesn't seem to do it. It copies down the original values, but does not allow for changes in the A1 value when pasting down rapidly.

Unless I am misinterpreting? Please let me know if so.

Appreciate your help mate.

[Bob Phillips]

The way I read that statement was that you were loading A1, which triggered changes gto B1:I1, and then you copied those VALUES down 999 rows.

Then you changed A1, and repeated the process.

And so on.

What I didn't get a feeling for was what you did with those values.

As far as I can see, both Test4 and Test5 seem to do that, although they do need a driver, something to load A1 and call the macro.

Am I missing your real intent here?

[xluser2007]

The way I read that statement was that you were loading A1, which triggered changes gto B1:I1, and then you copied those VALUES down 999 rows.

Then you changed A1, and repeated the process.

And so on.

What I didn't get a feeling for was what you did with those values.

As far as I can see, both Test4 and Test5 seem to do that, although they do need a driver, something to load A1 and call the macro.

Am I missing your real intent here?

I completely understand where you were coming from now.

I should explain the simulation process like this:

1. Set a value for A1, say 255.
2. Recalculate B1:I1 for this value of A1 (255).
3. Copy the values recalculated in B1:I1 down to B2:I2.
4. Reset a the next value for A1 (say 256), and recalculate B1:I1 for this new A1 value (256) and now paste in B3:I3, and so on for 1000 values of A1.

Hope this explains it better.

Please note you may ask why we don't just have A2:A1001 preloaded with input values, and why we don't just copy the formulas down and recalculate. The reason is that the calculations are pretty intensive and to minimise memory overhead I'd like the loop to evaluate the output row (i.e. the yellow highlighted cells in our example workbook) for a given input, and its more efficient to value paste down.

This way we only need to re-calculate a very small section (i.e. one row) and paste down and let VBA do a fast looping, hence this exercise. Also this particular worksheet is more suited and designed to use the proposed looping approach rather than autofill approach.

Your autofill solution provides a very efficient way to do this type of process (copying formulas down rapidly), but for this case I would like to know how to optimally loop-value paste.

Hope this helps clarify.

[Bob Phillips]

If you have to have each row being executed at different times, I don't see how a loop can be avoided. But ... I would not use Evaluate unless I had to as that is not efficient.

So my question now is, what is wrong with the loops you have shown (Test1, and 2). What is your objective from that point, speed improvement, just learning alternatives, or what.

I will check back in tomorrow, bed for me now.

[xluser2007]

If you have to have each row being executed at different times, I don't see how a loop can be avoided. But ... I would not use Evaluate unless I had to as that is not efficient.

So my question now is, what is wrong withg the loops you have shown (Test1, and 2). What is your objective fromn that point, speed improvemnet, just learning alternatives, or what.

I will check back in tomorrow, bed for me now.

You've pretty much nailed it there. I just wanted to know what was the optimal (time wise) approach of looping through an pasting values, looks like test1 and test2 are the way to go then (nothing wrong with them per se, just thought there may be even better ways to optimise them).

Based on johnske's article on Evaluate, I would have thought that this would be an incredibly efficient way to go.

As you also pointed out, I am always keen to learn alternatives from experts for even these simple copy-paste problems which will help me going forwards - which was another reason for this query.

On that note, could you please explain where I was going wrong in test3 (the Evaluate method) and from post#2, why my test2 wasn't working with the paste values method.

Aside: this could be done with a single Excel Data table (and looping avoided altogether), but from experience, where there are lots of links to otehr worksheets involved, data tables have a need to fully recalculate the workbook and all dependent cells. Thus a looping approach to effectively recreate a data table that calculates/ processes more efficiently is sought here.

Will catch up tomorrow.

Thanks again

[Bob Phillips]

I thought you had picked up this technique from an eralier thread by n8mills. I had showed him an Evaluate solution, and he tried to apply it to all his problems. As I said to hime there, Evaluate is not a panacea for all ails, it just won't work sometimes.

In your case, although Range("B1:I1") will evaluate to a range, if you want to apply it to a range of cells, it needs some extra logic, otherwise it just picks up the first value in the range, which is what you are seeing.

Johnske and I just disagree profoundly on Evaluate. He thinks it, and its shortcut manifestations, is good news. I think it is bad news and should be avoided unless absolutely necessary.