+ Reply to Thread
Results 1 to 25 of 25

Help with looping

  1. #1
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Help with looping

    Hello All,

    I have a vba that I'm trying to loop through but not sure how. I've attached the vba. What I can't seem to figure out is how to loop and increment x. Right now x="1-1", what I need is for for x to increment from "1-1" thru "1-14' then switch to "2-1" thru "2-14" and so on until "14-14". Plus I need the range("D28") to increment from D28 thru D41 along with the "1-1" thru "1-14" then switch to E28 thru E41 and continue thru Q41. Is this even possible? I hope this make sense.


    Thanks
    E.

    Please Login or Register  to view this content.
    Last edited by enhydra; 06-03-2011 at 08:16 PM.

  2. #2
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Help with looping

    Hi,

    Maybe something like this will help. If you post a dummy workbook I can test it.

    Please Login or Register  to view this content.
    Good luck,

    abousetta

    P.S. The example above uses 3 as your upper boundary. You can change this to 14, but its just a quicker example than looping through 9 Msgboxes than 196

  3. #3
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    The following code works for whatever sheet is active. It uses a 2D array of string values to compare to the Range array from the sheet:
    Please Login or Register  to view this content.
    Last edited by Mordred; 06-03-2011 at 12:12 AM.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  4. #4
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Ok, here are sample sheets that should work.
    Attached Files Attached Files
    Last edited by enhydra; 06-03-2011 at 01:59 AM.

  5. #5
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Hi Mordred, I tried your code and it stops at "Set LastRng = Range("C1000000").End(xlUp)"

  6. #6
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    When you say it stops, what does it tell you? Do you have more than 1,000,000 rows of data?

  7. #7
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    See the workbook attached. Before you click the Search button, confirm that no values are yet on Sheet2. Go back to Sheet1 and click the Search button. It works for me.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    That works but it only solves half have my problem. The last part of the code needs to loop thru a range where the "1-1" thru "14-14" will be placed. See my original. I probably wasn't very clear, but 1-1 would be placed in sheet "output" D28, 1-2 in sheet "output" E28.......thru Q28 then switch to row 29 all the way to 14-14 in sheet "output" Q41.

    thanks
    E.
    Last edited by enhydra; 06-03-2011 at 01:13 AM.

  9. #9
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    Sorry about that, I understand now but it is currently 12:21 am and I have to sign off for the evening. Perhaps someone else can continue on from where I left off or I will continue tomorrow (later today I guess).

  10. #10
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    I changed the workbook to show what I am trying to acheive
    Attached Files Attached Files

  11. #11
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    I don't understand how you want the final outcome to look. You say to place 1-1 in D-28, 1-2 in E28, and so one. What if 1-1 is repeated in the search, or any other value is repeated in the search? Example, say the first value is in fact 1-1, it goes into D28. Next, lets say the 3rd last value is once agian 1-1, does that go into D28 again? If so, why bother?

  12. #12
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Yes, it will go into D28. Once a *-* is found in a place it will not be found in a different place. The *-* number could be found anywhere in the designated area, D28 thru Q41.
    Last edited by enhydra; 06-03-2011 at 09:18 AM.

  13. #13
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    Alright, I see what I can do.

  14. #14
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Help with looping

    My problem now is I don't know how to apply a Sort to the string variables stored in the array. If someone else knows how to do that then great, otherwise this may take me a while.

  15. #15
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Help with looping

    Please Login or Register  to view this content.



  16. #16
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Thanks snb, but that code seems to be way off from what I'm trying to acheive.

  17. #17
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help with looping

    enhydra,

    Attached is a modified version of your workbook. I put a button named GetData on the Output sheet. That button is assigned to the following macro:
    Please Login or Register  to view this content.

    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Thanks tiger, but that doesn't work exactly the way I wanted.

    I'm going to try and explain this again. I know I'm not being very clear so please bear with me.

    I've attached an example. The input sheet has the data, I need to look thru the Column Location then the Row Location and find the cell then and put the Number Location in the appropriate cell on the Output sheet.

    I hope the example is clearer. The sheet(Output2) is what I trying to achieve. The way I have it working now is by repeating the module in my original post over and over execpt I'm changing the location number. This comes out to 280 Subs. Not good.

    Thanks

    E.
    Attached Files Attached Files
    Last edited by enhydra; 06-03-2011 at 06:30 PM.

  19. #19
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help with looping

    enhydra,

    Alright, now I'm really confused. What you're saying and what you're showing as the desired output aren't the same.

    In the file you just attached, you have an Ouput and an Output2. Output shows the results of my macro. Output2 shows the results you want (I think).

    The result 31-7 is in cell D12 of Output2. D12 should be looking for 1-1 in the Input sheet and returning the cell 3 to the right, but the result for finding 1-1 is 1-1, not 31-7. 31-7 is a result when looking for 10-14. Even if I try to use the numbers you've highlighted in yellow as leftnum-rightnum, I still don't get 31-7 as a result for 1-20 or 20-1.

    So i'm a little confused. What exactly are you looking for?

    ~tigeravatar

  20. #20
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    I'm trying to lookup a location from columns A and B on the input sheet, then copy from the data from either column D and E or the combined column F. The number in column F needs to go on the output sheet corresponding to location numbers from the input sheet. I use the data from F because I need a "-" between the numbers. And yes, the output2 sheet is how it should look. I put headers on the columns on the input sheet and titles over the Output sheets. maybe this will help explain what I'm trying to do.

    Thanks

    E.
    Attached Files Attached Files
    Last edited by enhydra; 06-03-2011 at 07:33 PM.

  21. #21
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help with looping

    enhydra,

    I set the cell format to General and in cell D12 put in the following formula:
    =IFERROR(VLOOKUP($C12&"-"&D$11,Input!$C$2:$F$241,4,FALSE),"")

    Then I copied over and down and it produced the desired results. No VBA necessary.
    Attached is a version of the workbook that contains the formula.

    Hope this helps,
    ~tigeravatar
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Thanks Tiger, but I need a VBA. There are other modules that run before this and none of the sheets exist in the beginning. The sheets are only created depending on certain conditions.

    Thanks

    E.

  23. #23
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help with looping

    enhydra,

    Updated button code and verified its working. Attached contains the button and macro:
    Please Login or Register  to view this content.


    Hope that helps,
    ~tigeravatar
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    12-03-2009
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    184

    Re: Help with looping

    Thank you, thank you, thank you. It works great. This will cut down my code so much. I was running into memory errors. You just made my day. I really do appreciate it. I will mark this as solved.

    Regards

    E.

  25. #25
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Help with looping

    If you use a row sequence from 1 to 20:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1