Excel VBA Copy PAste using For Loop: Slow Macro Execution

Question

Hi,

I have written For Loop for sending input data to Third Party application from Excel and then third party application runs and sends the output to excel , i is the row number in excel sheet .

But below code takes too much time to process input data and send the output as it has to change between different worksheets for that copy paste. I tried adding value after cells but not good speed still.

How to use any alternate way to resolve this issue . I am getting the code execution in around 455 secs for this macro and i want to make it around 50 secs . Is it possible ?

See the code below :

Dim Start_Date As Date

Dim End_Date As Date

Dim i As Integer

Dim Start_Row As Integer

Dim endrow As Integer

Start_Date = Worksheets("Series").Range("H8")

End_Date = Worksheets("Series").Range("H9")

Worksheets("INPUTOUTPUT").Range("P17") = 1

For i = 18 To 23

If Worksheets("Series").Cells(i, 4) = Start_Date Then

Start_Row = i

ElseIf Worksheets("Series").Cells(i, 4) = End_Date Then

endrow = i

End If

Next i

For i = Start_Row To endrow

'First Input Entry

Worksheets("SeriesData1").Range("AB14").Value = Worksheets("Series").Cells(i, 7).Value

Worksheets("SeriesData1").Range("AB15").Value = Worksheets("Series").Cells(i, 6).Value

Worksheets("SeriesData1").Range("AB16").Value = Worksheets("Series").Cells(i, 5).Value

Worksheets("SeriesData1").Range("AB17").Value = Worksheets("Series").Cells(i, 9).Value

Worksheets("SeriesData1").Range("AB18").Value = Worksheets("Series").Cells(i, 8).Value

'Second Entry

Worksheets("SeriesData1").Range("AB19").Value = Worksheets("Series").Cells(i, 11).Value

Worksheets("SeriesData1").Range("AB20").Value = Worksheets("Series").Cells(i, 10).Value

Worksheets("SeriesData1").Range("AB21").Value = Worksheets("Series").Cells(i, 13).Value

Worksheets("SeriesData1").Range("AB22").Value = Worksheets("Series").Cells(i, 12).Value

'Worksheets("SeriesData1").Range("D13") = Worksheets("Series").Cells(i, 8)

'Worksheets("SeriesData1").Range("D9") = Worksheets("Series").Cells(i, 26)

Answer 1

Hi

I'm Anna and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

One way to speed up your code is to use arrays to store the values from the "Series" worksheet, and then use a single statement to copy the values to the "SeriesData1" worksheet, rather than using multiple statements to copy each value individually.

Here's an example of how you can use arrays to speed up your code:

Dim arr As Variant arr = Worksheets("Series"). Range("E" & Start_Row & ":P" & endrow). Value

Worksheets("SeriesData1"). Range("AB14:AB22"). Value = arr

This code copies a range of values from the "Series" worksheet to an array, and then copies the entire array to the "SeriesData1" worksheet in one statement.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

Anna

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 2

Dear Sir,

Thanks for quick and correct response from you. This can solve my issue if you help me further.

I got your point, but My column numbers are not in consistent order like E to P for AB14 to AB22. For example, AB14 value from Series Data Worksheet is Column no 7 and corresponding row number in Series Worksheet,

I tried writing your code for my existing code, but it is not giving me correct input values, it is working but not giving correct set of values which i want from this.

Please note that i is row number, and each row is new next data for me and like this, i have data for say 3 years, and I have put For Loop to run and get input data for selected dates as per user entry for From Date and To Date field in Excel Cells.

See my post above:

I have below code:

Worksheets("SeriesData1").Range("AB14").Value = Worksheets("Series").Cells(i, 7).Value

Worksheets("SeriesData1").Range("AB15").Value = Worksheets("Series").Cells(i, 6).Value

Worksheets("SeriesData1").Range("AB16").Value = Worksheets("Series").Cells(i, 5).Value

Worksheets("SeriesData1").Range("AB17").Value = Worksheets("Series").Cells(i, 9).Value

Worksheets("SeriesData1").Range("AB18").Value = Worksheets("Series").Cells(i, 8).Value

You can see Though AB14 to AB22 are in order, but Corresponding Column Nos are not in Order like, 7, 6, 5, 9 and 8.

How can your code be written to store and get these values? Yori code will definitely help me in saving values for AB14 to AB22 but for Series Worksheet, Value is i,7 wherein i is the row number( each date) and 7 is column number. Hoep you got my point. Please look at my code carefully.and share new code.

Can you please write and convert my code sent in first post to your code and share it with me. Please include my For Loop also in your new code if possible.

Just convert it for One set of inputs only.

Regards,

Answer 3

Please find below input dataset here.

Answer 4

Hi there

I want to help you with your problem, I read your code and comments.

But I have some concerns, about the 2nd loop where you paste the values from sheet "Series" to the AB column cells in the "SeriesData1" sheet

For example, using the first line of code in the loop

Worksheets("SeriesData1").Range("AB14").Value = Worksheets("Series").Cells(i, 7).Value

Let's say Start_Row=19 and End _Row=22

So for i=19

Worksheets("SeriesData1").Range("AB14").Value = Worksheets("Series").Cells(19, 7).Value

therefore cell AB14 = G19

On the next loop

i=20

So Worksheets("SeriesData1").Range("AB14").Value = Worksheets("Series").Cells(20, 7).Value

meaning, cell AB14 will delete the previous value and get the value from cell G20 on the "Series" sheet

Once the loop finishes, the value in cell AB14 will always be the End_Row value on the "Series" sheet

i.e. G22

As it is

The loop will always overwrite the values in the AB column cells and at the end, it will show as a result, the End_Row data values

I might be wrong,

But ... are you sure that's what you want?

Regards

Jeovany

Answer 5

Dear Sir,

Thanks for your prompt reply.

You are exactly right in your understanding. That is what I wanted now.

My code format is

Step 1: Input Data processing for particular date say 8/01/2014

Step 2: Run Third Party Application

Step 3: Get the Output and sent it to Excel for that particular date.

Run these steps for all dates selected by users like 08/1/2014 to 09/01/2014.

Did you get my requirement.?

How to do this using VBA arrays?

Regards