• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Paste Array using Range

AAP

AAP

Member
In the below code variable prodg which is undimensional array captures the results and paste it back to worksheet one by one.
I would like that variable prodg hold the results of all the rows and paste it back once to worksheet not row by row. The reason just to increase the speed of the code. I uploaded a sample file with just 768 rows to find but in actual I have rows of about 900,000 which takes a lot of time. Can someone help me?

[/
Sub testusingranges()
Debug.Print Time
Application.ScreenUpdating = False
Dim MPNBase As Range, MPN As Range, FindMPN As Range
Dim prodg As Variant
Dim matchnumber As Long


Set MPNBase = ThisWorkbook.Worksheets("MPNBase").Range("Table1")
Set MPN = ThisWorkbook.Worksheets("MPNBase").Range("A2:A1000")
Set FindMPN = ThisWorkbook.Worksheets("FindMPN").Range("A1:A768")


For Each FindMPN In ThisWorkbook.Worksheets("FindMPN").Range("A1:A768")
If IsError(FindMPN.Value) = True Then GoTo Here:
On Error GoTo Here:
With Application
matchnumber = .Match(FindMPN, MPN, 0)
prodg = .Index(MPNBase, matchnumber, 0)
End With
FindMPN.Offset(0, 1).Resize(1, 3).Value = Application.Index(prodg, 0, Array(2, 6, 9))
Here:
On Error GoTo 0
Next FindMPN


Application.ScreenUpdating = True
Debug.Print Time
End Sub]
 

Attachments

  • Test.xlsm
    53.3 KB · Views: 2
NARAYANK991 said:
Hi ,

See how this performs on your 900,000 rows of data.

Narayan
Click to expand...

Many thanks Narayan, you always been a quick and powerful support for me. I tested on 30,000 rows previously it was taking 36 seconds after you update the code it took only 9 seconds. But now I am testing on 300,000 rows but seems it went into an unlimited loop because its almost 15 minutes and not finished yet.
Regards/Amit
 
AAP said:
Many thanks Narayan, you always been a quick and powerful support for me. I tested on 30,000 rows previously it was taking 36 seconds after you update the code it took only 9 seconds. But now I am testing on 300,000 rows but seems it went into an unlimited loop because its almost 15 minutes and not finished yet.
Regards/Amit
Click to expand...

Hi Again, That loop was not finished so I force close the excel. Now I tested on 100,000 rows and it ends up in 3 minutes. I couldn't understand the difference of 30,000 rows 9 seconds and 100,000 rows 3 miniutes running time difference. Do you have any understanding on it? thanks
Amit
 
AAP said:
Hi Again, That loop was not finished so I force close the excel. Now I tested on 100,000 rows and it ends up in 3 minutes. I couldn't understand the difference of 30,000 rows 9 seconds and 100,000 rows 3 miniutes running time difference. Do you have any understanding on it? thanks
Amit
Click to expand...

Hi again,
If the value not found in the table then match function throws error number 13 value not found. Could you please help me on it. thanks
 
Hi ,

There are too many posts , some of them offering advice , while others offer complaints.

I will stay out of the discussion and allow Deepak and Marc to have their say.

In case they have nothing more to offer , I will come back into the discussion.

Narayan
 
Marc L said:
Hi,

use a Variant variable with Match function and IsError function …
​
Click to expand...

Hi Marc, could you please write it in the code I tried but not working. In addition, please be careful if Iserror statement increase the code running time significantly. Thanks/Amit
 
NARAYANK991 said:
Hi ,

There are too many posts , some of them offering advice , while others offer complaints.

I will stay out of the discussion and allow Deepak and Marc to have their say.

In case they have nothing more to offer , I will come back into the discussion.

Narayan
Click to expand...

Sorry Narayan, I had no intention to complaint anything but just to learn something new. Regards/Amit
 
Hi Amit ,

No issues ; if you can post the file with 100,000 rows of data , it may be easier to understand why Excel is freezing.

As far as error handling is concerned , I have not made any changes to what ever was present in your code ; if your code was working on 100,000 rows of data , then there should not be any problem. However , if you can upload your file , I will check it out.

Narayan
 
Hi Narayan, its almost 14.2 mb file and couldn't upload on chandoo.org but here is the link below and its not .xlsm but .xlsb to reduce the size of file.

https://copy.com/yGWZrEXpxVVQawIl

Please check and if possible add on error statement so that missing values or #NA can be avoided.

Regards/Amit
 
Hi Amit ,

The problem has nothing to do with IsError or anything else within the code.

Forget the code , and try the following 2 formulae within the worksheet :

=MATCH(A1,Table1[Column1],0)

=MATCH(A1,Table1[Column1],1)

See the difference in timing.

Your data is so huge that if you use a MATCH with 0 as the last parameter , then Excel is not going to perform the fast binary search ; instead , it will be a cell-by-cell linear search which is several orders slower.

If you are sure that your data in the MPNBase tab will be in sorted order , then you can speed up the timings by several orders of magnitude , by using the second variant of the MATCH function.

Narayan
 
NARAYANK991 said:
Hi Amit ,

The problem has nothing to do with IsError or anything else within the code.

Forget the code , and try the following 2 formulae within the worksheet :

=MATCH(A1,Table1[Column1],0)

=MATCH(A1,Table1[Column1],1)

See the difference in timing.

Your data is so huge that if you use a MATCH with 0 as the last parameter , then Excel is not going to perform the fast binary search ; instead , it will be a cell-by-cell linear search which is several orders slower.

If you are sure that your data in the MPNBase tab will be in sorted order , then you can speed up the timings by several orders of magnitude , by using the second variant of the MATCH function.

Narayan
Click to expand...


Many many thanks Narayan, I believe that would be the good solution to the problem.

Regards/Amit
 
You must log in or register to reply here.
Back
Top