More efficient way to loop through cells and assign values

**welchs101** · 08-27-2013, 08:26 AM

Hi,

I need a more efficient way to loop through cells on several worksheets and return results to summary worksheet. I am attaching the code in module1 that i am using and i dont know how long it takes......all i know is that it takes longer than 20min.

I have 4 worksheets. I am trying to return values contained on the "TDY", "TCD", and "FullList" TO the summary sheet. To do this for example:
Between the Summary and TDY: Match the Subj_DS value between the two sheets (if there is one) and return the "days" value to the summary sheet.
Between the Summary and TCD: Match the Subj_DS value between the two sheets (if there is one) and return the "ReadStatus" value to the summary sheet.
Between the Summary and FullList: Match the Subj value (Note: Subj value only) between the two sheets (if there is one) and return the "StatusB" value to the summary sheet.

i am attaching the code and excel file.

************Note: The file i had was too big to attach so i had to reduce the number of rows to make the file smaller..........

the code i am using is also shown below:

Please Login or Register  to view this content.

**AB33** · 08-27-2013, 08:49 AM

This is not a code, but a formula. I have deleted most of the rows on the summary sheet in order to reduce the size of the file.

**welchs101** · 08-27-2013, 09:19 AM

thanks AB33 but i am looking for a VBA type soln. Is there an efficient vba soln out there? Mine certainly is not going to work.

**AB33** · 08-27-2013, 09:40 AM

Try the attached code.

**welchs101** · 08-27-2013, 10:01 AM

AB33 thanks for your help. How did you come up with that code so quick?

I took your formulas and some vba code and was able to get it to complete in 1.18 secs......which is way better than i thought it would.

I ran your code (again using the same dataset as above but usingthe FULL data set not the reduced one enclosed in this thread) and it took ~ 20 sec.

So i guess a combination of inserting formulas and using VBA is the most efficient way?

If anyone has a better way just let me know.

**AB33** · 08-27-2013, 10:22 AM

Generally speaking, Excel's built functions are normally faster than a macro, and looping through arrays are faster than looping through a range, but using dictionary can further increase the speed. My code is not entirely looping through arrays as it has got an element of looping through a collection of ranges.

You can get faster than my code if all the looping is carried out using arrays, but the tricky bit is matching the summary rows with each sheet rows, hence I had to use looping through a range . If all the sheets have had the same layout , there was no need for the last bit of the code. This probably would have saved another 5 seconds of the clock.

I think 20 seconds for over 10k rows across 4 sheets is not that bad.

**welchs101** · 08-27-2013, 01:58 PM

AB33,

I guess i spoke too soon...........so now the my approach is taking ~ 83sec..................

Why my test code took ~ 1.3sec and now when i put it into program it takes ~83sec...........i am guessing i have a formula some where that is attempting to recalculate but i am not sure.

Also, you said if you used arrays you could make it faster.........can you show me more about this not sure i understand.

**welchs101** · 08-27-2013, 02:15 PM

i am trying to go through your code now..........

can you tell me what this is doing

Please Login or Register  to view this content.

**AB33** · 08-27-2013, 02:27 PM

First, assign(or upload) the data in to array and then loop through the array

For e.g, the range of summary sheet is assigned to x variant

Please Login or Register  to view this content.

You then column 4 is added to dic
.Item(x(i, 3)) = x(i, 4)

When you run the code on your actual data, how long does it take to run it? Forget about a formula.

**AB33** · 08-27-2013, 02:46 PM

Try the attached. It might be faster than the previous code, but as I said, formula is much faster than a code.

**welchs101** · 08-27-2013, 04:26 PM

hi,

so this is adding col-4 to dictionary

Please Login or Register  to view this content.

but what i dont get is why the x(i,3)

dont know what this means...........what would happen if its x(i,2) instead?

**AB33** · 08-27-2013, 04:31 PM

You are testing column 3 for a match.
The concept is s very similar to Index and match formula, Match two columns and return the corresponding row-index.
You are matching column3 of sheet summary with the other sheets and then return the index of other sheets(column4) to summary sheet.
.Item(x(i, 3)) = x(i, 4)

**AB33** · 08-27-2013, 04:33 PM

By the way, have you tested the latest code and if so, did you see any change in a speed? The code is also shorter than the previous one.

**welchs101** · 08-27-2013, 05:09 PM

have not tested it yet......after dinner i will test it and get back to you. thanks.

also, i have been meaning to learn about scripting.dictionary stuff......any good web sites or info you recommend because i cant find it in any books.....

**AB33** · 08-27-2013, 05:37 PM

I know!
You have asked similar question in the past. I remember Jindon had given you some info.
I was in your position a year ago. I started on this site (See attached).
IMO, it would be much easier to first master loading data in to array and copying the loaded array back in to range. It then becomes easier to learn dictionary.

http://www.experts-exchange.com/Soft...ss-in-VBA.html

**welchs101** · 08-27-2013, 05:57 PM

i get a run time error at this step

Please Login or Register  to view this content.

**AB33** · 08-27-2013, 06:30 PM

Where? Is it in your actual code, or in the sample?

**welchs101** · 08-27-2013, 09:23 PM

its in the code example you sent ........the latest one.......but i used in my "FULL" data set.

**AB33** · 08-28-2013, 02:56 AM

If the layout of the your actual data has not changed since you run the first code, I do not know why you are getting an error. The number of columns has remained the same-4.

**snb** · 08-28-2013, 04:14 AM

Please Login or Register  to view this content.

NB. I altered the misspelled sheetname FullLlist into 'List'

**welchs101** · 08-28-2013, 06:45 AM

trying to work through the code to understand whats going on.

is there a way to display the dictionary.........it would be helpful to me if i could see whats actually in the dictionary

is this even possible?

**AB33** · 08-28-2013, 07:04 AM

The link I had sent will show you how the dic and arrays work.
I do not think you may find many free material on dic. Whatever you find on-line is not specifically geared to-wards excel.The best way to learn is by "Doing"

**welchs101** · 08-28-2013, 07:09 AM

AB33, i double checked as far as i can tell i have not changed any of the worksheet formats or column designations. Still i dont know why i am getting a type mismatch error when i try to run your code. I am sure it is something simple.......just dont know what it is.

snb, i had a question about your code (note: anyone can answer the question):
In the code you have the following

Please Login or Register  to view this content.

can you describe what this is doing?
What i think i know is this.......and please correct me if i am wrong.
The key for the dictionary is the 3rd column of values (Subj_Ds).
There are 5 values for each key: sn(j, 1), sn(j, 2), sn(j, 3), sn(j, 4), ""

?How would you access value 3 [sn(j,3)] for a certain key?...........

Also in reviewing snb's code i dont understand the role of "st" and what it is....can someone help me understand whats going on below:

Please Login or Register  to view this content.

**AB33** · 08-28-2013, 07:19 AM

I really do not know what causes the error as you did not get an error on the first code.

**welchs101** · 08-28-2013, 07:24 AM

also if someone could explain this i would appreciate it..........

Please Login or Register  to view this content.

i know its pasting something onto the summary worksheet but not sure what?

**welchs101** · 08-28-2013, 07:35 AM

Lastly,

if someone could explain this it would be helpful:

Please Login or Register  to view this content.

i know it says something about go to first column of the summary worksheet and find the value in sq array but then i dont get the .offset or why sq(j,2) is being assigned ..........just confused. please help.

**welchs101** · 08-28-2013, 07:53 AM

AB33,

in the first code you did not have

Please Login or Register  to view this content.

you had this

Please Login or Register  to view this content.

i get the type mismatch at

Please Login or Register  to view this content.

**AB33** · 08-28-2013, 08:08 AM

Try to use this line

Please Login or Register  to view this content.

Instead of

Please Login or Register  to view this content.

Do you still have column 4 as x(i, 4) refers to column 4?

**snb** · 08-28-2013, 08:09 AM

Did you run the code ?

Did you run the code step by step ?
Did you make the window 'local' in the VBEditor visible ?

**welchs101** · 08-28-2013, 08:10 AM

AB33,

I figured out what was causing the problem.......the layout was the same.
The problem was my FULL-data set on the TDY worksheet in the column of "Days" there were "#Values"............i reassigned all the "#Values" to a value of 993 and the macro ran just fine.

also, why did the #Values cause a problem?

have not verified it yet.........but had a question

can you explain what this is doing?

Please Login or Register  to view this content.

**AB33** · 08-28-2013, 08:12 AM

I have re-run the code on the sample, I did not get any error and the code completes in 3 seconds.

**AB33** · 08-28-2013, 08:17 AM

Well,
It is an error value and the code will show that.
If you have got formulas in your data, please use iferror excel's function to trap the error.

Please Login or Register  to view this content.

**welchs101** · 08-28-2013, 08:23 AM

snb,

i have not "stepped" through the code. I have reviwed the code put hard breaks in it where i thought i should learn something.

I may know that the following code puts results i want onto summary sheet but i dont understand why?

Please Login or Register  to view this content.

**welchs101** · 08-28-2013, 08:25 AM

snb, can you also explain a little this code?

Please Login or Register  to view this content.

i know whats happening i just dont know why.....hopin you could shed some light.

**welchs101** · 08-28-2013, 09:02 AM

any way to display key and items in dictionary so i can see whats "going on"

**welchs101** · 08-28-2013, 09:22 AM

AB33 and snb,

I hope you're both still there............

I took the examples you both gave and came up with this approach........trying to learn this as well. I think i have all the data into the dictionary but now i need to somehow get it out of the dictionary and into the correct col locations...........

please help if you can?

also, if there is something fundamentlly wrong with my approach or fundamentally better about yours please let me know.

Please Login or Register  to view this content.

**welchs101** · 08-28-2013, 09:52 AM

anyone? so close to end.............can someone help?

**jindon** · 08-28-2013, 10:07 AM

I haven't read all through the history, so worked with the file.

See if this is what you are after.

**welchs101** · 08-28-2013, 10:27 AM

thanks jindon,

i will take a look at your code as well.

But i really spent a lot of time learning AB33 and snb's code.................i took parts from each and came up with my own version. Problem is i dont know how to get the data out of the dictionary............

i will review your code thanks.

**welchs101** · 08-28-2013, 11:31 AM

i scrapped my previous attempt and went with something that resembles what AB33 did...........now keep in mind i am just starting to learn about these dictionary things and while jindon's code and snb's code work and are very fast...........there are things in the code i dont understand. I understand AB33 code........so i am using a variation of it.......

enclosing code below:

Please Login or Register  to view this content.

**snb** · 08-29-2013, 05:54 AM

Please Login or Register  to view this content.

Writes any 2-dimensional array into a worksheet.
See the explanation for 'index' in the Excel formulae.
In this case VBA uses an Excel function.

You will have to apply this method to 'understand' what it does.

More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Re: More efficient way to loop through cells and assign values

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions