LinkBack URL
About LinkBacks
Hi,
My macro runs around 20 min.. for 12000 lines..
Is it normal, or should i try different logic.. how to speed up..
Hi,
My macro runs around 20 min.. for 12000 lines..
Is it normal, or should i try different logic.. how to speed up..
This does not seem all that efficient. If you want somebody to take a look, post the code your using along with the code tags.
But in the meantime, you could Google the net for "excel speed up macros"
One such hit >> https://www.computergaga.com/blog/6-...-excel-macros/
Also, if you happen to have a lot of .Selects in your code, that could be dragging you down.
See >> http://www.tushar-mehta.com/excel/vb...acro_recorder/
HTH
Regards, Jeff
Hi jeffrey,
I have attached the code in the file.. But not provided data..
My purpose is.. based on company criteria , i am consolidating the sheet to main sheet.
for each company you have different dates.. so in the main sheet with already company being populated, i am checking the company to be same?
if same, copy the date, else add an row and insert the company and date..
In this, since i have already sorted, i am inserting row will not have any issues for checking.
if the company is not same, i am also checking, whether previous line of row has this company name, as the row ranges are different. I am checking this.
Finally when both the scenarios are not possible, i am checking for the column and copying to that row.. Like this i am doing for 12000 lines in main sheet and also the to be copied data sheet has 12000 lines.
so 24000 lines are matched and checked..
Is my code be trimmed or modified?
Without any dummy data, it is hard to understand what you are trying to do. If I have understood correctly, you have 2 sets of data on different sheets. You are trying to compare one to the other then update or insert rows depending on whether there is a match or not.
Since you are performing all the read/write/insert actions within the worksheets, this process will be painfully slow. What you want to use are arrays.
You read the ranges into arrays then compare the arrays to each other using VBA. Carry out all of the add/update operations within the arrays without touching the sheets at all. When you are finished, erase the ranges and write the arrays to the worksheet ranges.
Think of this as carrying out all of your changes in memory instead of repeatedly hammering the workbook file.
Using arrays over worksheets to process your data will vastly decrease your running time. By at least an order of magnitude. Anecdotal reports up to 60 times improvement.
*******************************************************
HELP WANTED! (Links to Forum threads)
Trying to create reusable code for Custom Events at Workbook (not Application) level
*******************************************************
Hi Mc84,
I am not familiar with arrays..
if i provide dummy data , can you modify... as i have a 3 files like this..
So i can incorporate to others , how you have changed..
I have attached the file with dummy date..
Kindly change sheet 2 tab name.. from that only copied to main sheet
Last edited by dorabajji; 09-27-2019 at 09:15 AM.
Hi,
Can anyone.. let me know how to use array for consolidating in the above file..
as my macro is too slow.. mc84 suggested to use array.. But i am not familiar, can anyone provide me an idea or modify my existing macro..
as i need it asap to complete.. Can anyone help?
And one more doubt , i have.. i have not used variable for cell range..
If i use variable , instead of direct reference, will it speed up the process?
Sorry dorabajji, not sure I'm equipped in VBA to do what you ask, but I've put in a call for help.
Why would you not supply a workbook that matches your code?
Nothing to do what you asked for but you could clean your "main" sheet also. Use "Ctrl + End" to see the size.
Hi daves,
This is the code and sample file.. is from the main file.. i did delete 2 cols, and i didnot change sheet tab.. rest the same.. with trimming the cells..
i had attached the workbook second time with the sheets updated?
My code is checking the names, if found copying the dates, if not found inserting lines..
In finding..
i did three condition..
checking current cell, if not previous cell, if not then full column.. if not inserting the row and pasting..
My problem is since rows running 11000 odd lines, i had not used variable and direct cells , so it was taking around 20 min..
But as per jef suggestion, and mc86 , array needs to be used..
But i dont understand how to use array..
I have changed the code to variable for cell ref..
but how store in array, i am not getting it..
Hi Jef ,
Thanks for your help.. those links were useful to me..
Hi dav,
In the workbook
i have 2 sheets-main, sheet 2..(name to be changed)
In the main, already names with date and num are updated in COl A, B, C.
Now i need to copy col c from other sheet and update col d in main sheet..
The other sheet has 11895 lines..
Main sheet has 12000 odd lines..
With the conditions, i gave in the code, the previous people suggested to use array, thats what i want to know how..
I have changed the code to variable and store the cell ref in variable..
I am not familiar with array,.. if you want i will again reattach the workbook..
I will trim the names and date.. as my motive is to copy and not do manipulation within the cell and the file actual is confidential .. thats y..
Can you try..
Hi mc84excel,
Second attachment is the correct workbook.. If possible, kindly see the second one.. if thats also issue.. I will again reattach..
I am reattaching the same workbook..Kindly look into that..
My requirement is how to use array instead of my coding.
Last edited by dorabajji; 09-27-2019 at 06:44 PM.
Please Login or Register to view this content.
Hi ikboy,
ReDim a(1 To UBound(mn), 1 To 1)
can explain me this redimension..
And .. i ran your code.. i have 11963 lines... but the result has 10659...
i am debugging and understanding.. but i couldnt get where is the range not picking..instead of current region.. can i give last row..
d.exist(k).. what does its functionality..
If possible, can you explain me , so that i can understand better and use this in remaining by modifying..
I understand you are putting the names in array with repeated ones..
Hi Ikboy,
I ran your code.. understood quite a few..
In that.. one problem.. when the col a in both the sheets matching.. the results are good.. but when not matching.. ie when there is no name in the main sheet col A, then a row has to be inserted and copy the other sheet row..
The condition to be satisfied is..
COl A in both the sheets row by row should be checked whether there is a match.. if so, then copy col C that row value..
if not , check previous row for the same name.. , if that is matched then insert a row beneath that and copy ..
if there is no match on direct row comparison, check full column and when matches go to that row and copy..
then from there the row number is taken from that cell future checking, as the datas are sorted- previous become obselete for comparison , once row no is updated.
In the array , you had provided.. with what i understand is that you are taking the names and corresponding values into array and in the same way you are doing it to other sheet..
In both you are transfering the values...
In this i could find the names missing are not considered.. and but still i have variance..
ranges are correct.. i am not able to find out why around 1300 entries are not taken..
if you could , kindly look into that..
Last edited by dorabajji; 09-28-2019 at 05:45 AM.
Check your data after A10659.. are there have empty row? If yes, you just remove it or amend my code.
Hi Yes, The data is there after that row..
How to ammend.. your range is taking till 12000..
I am not getting where to change..
Please simulate the related problems file and upload to here. That will easy for testing.
Amended for having empty rows
Please Login or Register to view this content.
Hi ikboy,
In the same file copytestforum uploaded.. Two sheets- Hyr and one main sheet..
Main sheet has names and date in 2 coln.. one date and other in general format..
In col D, i need to copy HYR data..
In that checking is with the names.. if the names are matching, your solution is perfect..
When not matching , i need to insert the data copied from HYR to main sheet.. as all the names are not having perfect match..
In this for eg name 3d as 7 dates in HYR sheet, where as in main sheet the count is 5..
So for other 2, the data needs to be inserted beneath the 3d in main sheet by inserting row..
In your code, that is not taking into account..
I am not sure how to ammend it..
This is the file..
Main sheet has 11081 lines..
Hyr has 11963 lines..
Copying data is perfect..
But whereever extra data is there in HYR , it needs to be inserted in main sheet..
that is not happening..
Hi dorabajji,
Although surely not my strength, your question in post #14 might be helped by reviewing the link below...
https://excelmacromastery.com/excel-vba-array/
Hi ikbor,
Like len(k)>0,
there should also be a else condition when if d.exist(k) does not exist.. in the main sheet ..
so else,
when item name is not there.. it should be also inserted..
I have ammended ubound(mn) to hy for a..
to reflect the rows of hy , as it has more no of rows than main sheet..
Last edited by dorabajji; 09-28-2019 at 10:57 AM.
Hi Jef,
Thanks a lot..
I am getting to know more things from you guys..
I already started reading the links you gave yesterdays..
Thank you..
Admittedly I've come late to this, but is it certain that normal Excel functions can't be used here, perhaps with heler columns.
I've not studied all the posts and admit I don't entirely understand the overall goal but I'm guessig that the dates (or specifically the dd/mm expression of date) that are required in column D of the Main sheet is related in some way to the 2 character Heading ref on the Half Year sheet and the Heading 2 date.
Rather than trying to 'reverse engineer' the macro can you provide a simple set of rules that moves you from your data to the column D results.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Ikbor,
I have understood the problem... The variance is due to not taking into the count of items in HYr sheet.. For eg: the count is 5 in main and 7 in hyr..
Then the values are transfered till 5 only..
But the other 2 is omitted..
These 2 should be added below by inserting row.. that is not happening in the code..
That is the issue.. not blanks..
How to include this condition and ammend the code?
Originally Posted by Richard Buttrey
Hi Richard,
Actually the requirement is to copy dates based on name match.. where there is no match insert row in main sheet and copy..
Since I did normal.. it took 20 min..to run
But ikbor had used array and it was almost good..
But one problem is where there is no match it is not adding row and copying..
I am struggling to amend array for this..
If even the array cannot be disturbed, I want to add at last so that I can sort..
But I am struggling to amend array..
I tried , but since the object checks main sheet row, it exist so other condition of hyr sheet items not existing in main sheet also copied is not happening..
If you have any inputs or suggestion, kindly provide..
And also if any one can explain me the object declaration and
Array part in ikbor code it would help..
So that I can have a start..
Yes, ikboy's code is very fast.
Looking forward to his amendment to insert the rows when there are more in half sheet than main sheet.
I was actually questioning whether you need a macro.
If you state the rules as I suggested in my last post (I can't see wnywhere in the thread that these have been described) it's possible that some straightforward Excel functions might suffice.
Hi Ikboy,
From your code..
T(0)=7 t(0)=5
while transfering, it does not complete full quota.. that is the issue and it reads next row cell..
THis arise because main sheet has less count of names than hyr sheet..
If you could ammend , i would able to complete this.. I am trying to change , but it not giving correct result.
Hi Richard,
You have any idea of doing it in function, let me know..
total 12000 lines..
Copying on condition col A to be same, where no common , insert a row and copy col c and col a from hyr sheet to main sheet..
inserting row also happens when main sheet name count is less than hyr name count..
If you have any suggestion welcome..
Hi Ikboy,Please Login or Register to view this content.
I tried to change the code like.. this..
by transfering the missing cells to new array.. But it also not giving correct result?
Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.
Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
Don't assume we are able to "figure it out". We're usually quite good but not prescient.
Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.
.
What I was looking for was specific explanations which clearly explain (referencing all relevant cells and rules) where the values in the results column come from
Hi Richard,
I am sorry, i didnot understand..
I didnot get your point previously..
See, i want to copy HYR sheet into Mainsheet..
COL A in hyr is compared Col A.. by each row..
So when the row matches, then col C of HYR is copied to main..
But when not..
i will explain what i did.. (all data are sorted)..
I gave in my macro 3 condition...
One when matches,
copy into the same row from hyr sheet col c..
when it does not match.. check whether prev entry matches to hyr sheet for col a..
if it then insert the row in main sheet after the active cell and copy..
in this case both col a and c are copied from HYR sheet.
But when it doesnot match into prev row..
Then i used find function and where there is a match goto that cell and copy col C of HYR sheet..
then loop continues from there..
THis was my original macro.. But it took 20 min to run.. what ikboy gave me was almost perfect 1303 entries being left out due to array not including the missing items..
Requirement is consolidation without duplication..
Based on name criteria in col a in main sheet , col C of Hyr sheet are copied to COl D of main sheet..
i have posted workbook sample too..
but i have trimmed the names and date..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
,
Bookmarks