VBA For loop

**iamGreenhorn** · 07-14-2014, 10:30 AM

I need a little help getting in the right direction and I should be fine from there.

So the background here is i have a huge 10K line data file I need to search through and find up to three unique results given two conditions. the first condition has a possible of 93 outcomes and the second condition has a possible of 5 outcomes; for a total of about 10k random entries. I want to grow this function to fill lots of data but I keep getting random issues. consistently "error 1004".

This 10k file is updated constantly and i need to create reports for every year in the future so you're help here is really appreciated.

Please Login or Register  to view this content.

**watersev** · 07-14-2014, 11:00 AM

hi iamGreenhorn, welcome to ExcelForum

Can you provide details on the code to do and, if possible, a sample file (5-10 rows of data will be enough). It would also be helpful to get the result from the sample data you expect to get.

**iamGreenhorn** · 07-14-2014, 11:05 AM

I cant really provide the data but:

Im just trying to get it to loop through every row and if the two conditions here are met:

If Worksheets("2015 DATA").Cells(z, 9).Value = "CO" And Worksheets("2015 DATA").Cells(z, 27).Value = "class 12" Then

i want to write it to a cell. I was using the three variables as a way to not grab the same result three times aka it gets reset before the loop is entered and finds three different results.

Im trying to write these to a box at the end just to test functionality

**iamGreenhorn** · 07-14-2014, 11:07 AM

Im going through each row with the Z for loop
for every row we test two columns for their values (column 9 & 27)
If the values in 9 & 27 pass we write the value in column 7 row Z to a variable and then move on to the next row untill we finish all rows

**shg** · 07-14-2014, 11:13 AM

Why not just use Autofilter?

**iamGreenhorn** · 07-14-2014, 11:14 AM

Originally Posted by shg

Why not just use Autofilter?

Because i started vba last thursday :D

Is there a was to autofilter just down to the results. Like the possible answers?

**shg** · 07-14-2014, 11:16 AM

Filter column I for "CO" and column AA for "class 12"

What you see are just the rows that meet both criteria.

**iamGreenhorn** · 07-14-2014, 11:18 AM

That is fine but i have in the past used a formula that works, plus as i said there are a lot more results than i wanna deal with on a daily basis. I want to use VBA to remove the hassle and inaccuracy of that.

Please Login or Register  to view this content.

**shg** · 07-14-2014, 11:19 AM

If autofiltering gives the result you want, you can to that with VBA if you wish -- a few thousand times faster than what you have.

**iamGreenhorn** · 07-14-2014, 11:22 AM

Even with autofilter it doesnt boil down the results. i could sill have 100 mixed up entries and i could make it pull the first three unique entries but i still have the same loop and issue. Unless theres a way to use autofilter to get the possible results... like the boxes you check when you manually filter.. Does that exist as a quick google doesnt reveal it

Also efficiency isnt really relevant if it takes a few minutes i can live

If its really slow i can autofilter then run the loop.

**shg** · 07-14-2014, 11:27 AM

Please Login or Register  to view this content.

**watersev** · 07-14-2014, 03:07 PM

option basing on your code:

Please Login or Register  to view this content.

**iamGreenhorn** · 07-15-2014, 04:49 AM

Watersev, very nice code. thoughtful error handles.. very nice.
But when I use it I get "runtime error 1004, Application-defined or object-defined error"
It makes a new sheet and throws that error.

If i quote out "Sheets.Add.Range("a1:a" & j) = result" It doesn't error but it still creates a blank sheet.
I added a msgbox at the end and tried to display different things to see if it otherwise worked, it does calculate lrow correctly.. And if i print mystr it gives me every unique value in a string. :thumbsup

I am new to this and this environment, im sure im implementing it wrong or something..

Also I could use a little help understanding whats going on here:

If InStr(mystr, " " & arr7(i, 1) & " ") = 0 Then
mystr = mystr & " " & arr7(i, 1) & " "
j = j + 1
result(j, 1) = arr7(i, 1)

**watersev** · 07-15-2014, 04:56 AM

hi there, updated code

Please Login or Register  to view this content.

The Instr part helps to identify unique items in 7th column

**iamGreenhorn** · 07-15-2014, 05:01 AM

Thank you so much, hopefully i can apply this properly

**watersev** · 07-15-2014, 05:07 AM

The code was written blindly without seeing the actual data. Sometimes that results in some changes being required to the code

**iamGreenhorn** · 07-16-2014, 10:53 AM

Watersev,

I just about finished this project but when i run your block of code inside another for loop to fill an array it doesnt work.
It keeps filling the results from the first search. It seems if i change j it works but i can have that many variables.. If i put redim at the beginning or end of the first loop it doesnt fill anymore of the array.

What can i do to run your block back to back, Thankyou in advance!

**watersev** · 07-16-2014, 11:17 AM

without your code being posted I'm not able to answer your question

Why can't you have as many variables as you need?

**iamGreenhorn** · 07-16-2014, 11:41 AM

Please Login or Register  to view this content.

The only way i could get it to work for more than one was how its listed above, i tried to use it as a method, i tried to put it as the third loop in but nothing would work
:/

**iamGreenhorn** · 07-16-2014, 12:13 PM

I guess i should explain it,

Im trying to run three for loops.
1 adjusts for the X values across
2 adjusts for the y values
3 looks up the codes in OOT

What im saying is if i continued the only way i can get it to work, i would need 500 variables and thats hardly a good use of anyone's time lol.

**watersev** · 07-16-2014, 06:21 PM

my attempt to interpret your code (I hope I got it right mostly), press Run button or run code "thoughts" (ALT+F8, thoughts, Run)

I suppose it's not final yet but that's what we have for now

**iamGreenhorn** · 07-17-2014, 05:07 AM

Just downloaded and Im going through it. I feel like I really bit off more than I could chew with this project.. I have a ton of other functionality to complete by the end of work today, if you can help me with this It'll really save me.. If you cant, you have already helped me and I understand..

I really didnt wanna give out a lot of info

So Ill also attach your file with the change I was going after, I need a for loop that grabs either the names out of the first row of cells or pulls them from the listbox and then searches OOT data and fills that.

If theres anything else that would help let me know!

**watersev** · 07-17-2014, 05:59 AM

I do not mind to help if I can. I need details then. You can omit confidential info as I did with a sample file with the same structure but meaningless data.

In the posted file the Mastersheet contains results as they should be as I understand. Why is "candy" missing for Class 12? Do you need four first values only?

**iamGreenhorn** · 07-17-2014, 06:09 AM

Originally Posted by watersev

I do not mind to help if I can. I need details then. You can omit confidential info as I did with a sample file with the same structure but meaningless data.

In the posted file the Mastersheet contains results as they should be as I understand. Why is "candy" missing for Class 12? Do you need four first values only?

I really need only the first few to fill the box but the data SHOULD always support that anyway..

If you look at the picture, and if you really need I can upload the sheet, The issue is that in OOT data changes in each column. Each column is populated from the user imputed listbox then this loop needs to go through each column and find the data for the classes of the countries. I ran it one to four and tried changing the value to make it run through different countries and added fake data. In the end i will run it through to the listbox count (max 93).

I was asked to make a template but i decided i wanted to try my hand at vba, i really didnt have the time and i kept increasing the complexity of this project lol. I guess a week isnt enough time anyway...

**iamGreenhorn** · 07-17-2014, 06:17 AM

check below

**watersev** · 07-17-2014, 08:16 AM

I will check it out and post for your review

**iamGreenhorn** · 07-17-2014, 08:17 AM

Originally Posted by watersev

I will check it out and post for your review

I have a better one for you here. one second

**iamGreenhorn** · 07-17-2014, 08:20 AM

project.xlsm

Here i added a little code to change the countries,

I suggest you move all the items in the listbox from right to left with the "<<" then add a few like china, usa.. countries with lots of data then click build.

Then navigate to module one, i think youll find everything there..
Thanks again!

Just noticed i removed Col A from OOT so youll need to make a row a so lrow works

**watersev** · 07-17-2014, 09:14 AM

please check attachment

I have corrected some Listboxes codes a bit, some clean up on BuildFiles code and Thoughts code has been assigned to "Fill from OOT Data" button

**iamGreenhorn** · 07-17-2014, 09:29 AM

Works flawlessly

You're the man. If I'm ever in the ukraine, ill buy you a beer.

**watersev** · 07-17-2014, 09:38 AM

if we are done you can mark the thread as Solved as per Forum Rules. If any questions you can still make posts here afterwards, I have subscribed for it.

**iamGreenhorn** · 07-17-2014, 09:40 AM

I was hesitant to because i didnt know if i could still post, but good to know.
I will shortly, and i largely understand the concept of whats going on but I may have some questions

**watersev** · 07-17-2014, 09:58 AM

ok, cheers for now

**iamGreenhorn** · 07-17-2014, 11:41 AM

It seems to throw an error with but with no consistency. I have tried to labeling the variables as a static ...

Please Login or Register  to view this content.

I just piled all the code together and will built the databases first. but how do i fix that?

**watersev** · 07-17-2014, 04:53 PM

check value of the variable cur00T and how it gets its value

To declare sh variable:

Please Login or Register  to view this content.

VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Re: VBA For loop

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy dynamically changing column and Paste using VBA Loop (Loop within Loop)

Bookmarks

Bookmarks

Posting Permissions