+ Reply to Thread
Results 1 to 28 of 28

Explanation VBA FOR...NEXT loop

  1. #1
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Question Explanation VBA FOR...NEXT loop

    In the procedure below, could someone explain to me what each line in the FOR...NEXT loop is specifically directing the procedure to do?
    Is there a simpler way to code this part?
    Thank you very much!!

    Please Login or Register  to view this content.
    burnettec

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Explanation VBA FOR...NEXT loop

    Please Login or Register  to view this content.
    You could write it to make it easier to understand.
    You are looping through range "F" up to the last row(LR) which is decided by the last row in column "A". You are testing the if range(F:H)= 0, if it is you are copying Range("A:H") in to worksheet(ws)

  3. #3
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Explanation VBA FOR...NEXT loop

    I think this might explain it

    Please Login or Register  to view this content.
    this finds the last row of used data in column A and sets i to be between 4 and the last row of data
    it then checks all those rows assigned to i
    lets say i = 4
    Please Login or Register  to view this content.
    .Resize extends the range by (column,Row), in this case extends the colums only
    this sums the range( F4:I4)
    if it 0 then
    Please Login or Register  to view this content.
    this assigns worksheet( "violators") which has been set as wsNonV to ws if the sum was 0
    otherwise its assigns worksheet ("NONviolators") which has been set as wsV to ws if the sum was not 0
    Please Login or Register  to view this content.
    The next piece of code copies the range (again lets say i = 4) and then pastes it
    Please Login or Register  to view this content.
    copies range A4:I4 to the next row of the assigned worksheet
    Please Login or Register  to view this content.
    then calls for the next row to be checked if the sum is 0
    Please Login or Register  to view this content.
    hope this helps
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  4. #4
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Explanation VBA FOR...NEXT loop

    Hi Sean,
    Please Login or Register  to view this content.
    I thought this goes from A to H. Resize, unlike offset starts at 1, not 0

  5. #5
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    This is a great exchange. You have helped me a lot. I was a bit confused about xlUp instead of xlDown, and I've never encountered the Resize method. The columns do span A-H, but I"m so glad there was a misinterpretation because I did not know about Resize vs Offset. I'm more familiar with Offset.

    I was just taught that you do not need to include objects in an expression that are obvious. So, I used my objects browser to get the hierarchy of Application.Sum... and if Application is an object, Sum is not in the list of "Members". Could you explain that? I would think that the complete expression would be Excel.WorksheetFunction.Sum. Does "Application" routinely replace the obvious "Excel.WorksheetFunction"? [THanks!!]

  6. #6
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Explanation VBA FOR...NEXT loop

    "Excel.WorksheetFunction"? and Application.Sum
    are the same, except, if you have old excel version of excel, it would not cope with WorksheetFunction, but Application could handle any version

    Please Login or Register  to view this content.
    As VBA does not have a "Sum" function of its own, you are accessing excel's function of "Sum", hence you are using prefix "Application"

  7. #7
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    AH! Good to know. THank you. YOu've been so helpful.
    I am making a new procedure based on this one that adds 3 more sheets to my workbook. It takes "violators" data and identifies (then pastes into new worksheets) 3 violator types. I have replaced:
    Please Login or Register  to view this content.
    with:
    Please Login or Register  to view this content.
    and needless to say, it is getting hung up at the beginning of this If statement. HEre is my new procedure that doesn't work:
    Please Login or Register  to view this content.
    Ideally, I would like to have the ranges in those If statements to use a name instead of columnIDs.
    Should I submit help for debugging this in a separate thread? or would any of you have time/patience to investigate this? Thank you!

  8. #8
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Explanation VBA FOR...NEXT loop

    burnettec,
    I would use one of these codes. If you have a fixed range(4 to 20), you do not need to set loop, as you can see from your code, you are not using the loop(i) in your code. Or, if you have a varible range, you can set a loop. This loop goes down 4 to the last row in column "A"
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    Thank you for your reply. I will try this after lunch and post the results at that time. I tried my own If...ElseIF code last night, but I kept getting an error about not having an If block or some such thing. I'm sure it was some other syntax I missed. I need to study your "variable range" loop comment. Not sure I understand that. The 4 to 20 is just for a test sheet I'm practicing in. My real workbook will have named ranges "MW", "OT", and "CL" for the respective columns they inhabit. But I would need to declare ranges first and then use the names in place of ("F4" & I) for example? Like:
    Dim rMWRange as Range
    Dim rOTRange as Range
    Dim rCLRange as Range
    set rMWRange = "MW"
    etc.
    or am I completely off track (probably)? I just took a 3-day VBA course 2wks ago so am struggling a little with the concepts.
    Ugh.

  10. #10
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Explanation VBA FOR...NEXT loop

    "variable range" loop comment. Not sure I understand that"
    Variable, because, you do not want you range to be fixed. 4 to 20 is a fixed range. 4 to something is not. For e.g you may have in column "F" 4 to I. The I is a varible row. F is the column, but i, starting from 4 to the last data you have in column F, it may be 20,21, 100, or 5. It all depends on your data. 4 to 20 is fixed, so not need to look at row. The row is fixed from 4 to 20. If you have data after row 20, the fixed range will not look at it, but having i as flexible row, you range is covered if you go beyond 20, or you have your data in column F, goes down , let say to 6, or 7. The I only goes down to 6, or 7

  11. #11
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    I understood the variable part, but not the loop. I understand now that you mean (F: &i&) is the loop. Yes, I'd like to keep it variable. If the NEXT encounter in the loop is a blank cell, will it keep going because I've turned off error handling? I'm in the process of testing the If/ElseIf by the way. Will follow up later. THanks.

  12. #12
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Explanation VBA FOR...NEXT loop

    Please Login or Register  to view this content.
    The code first looks at Column F,row 4, second loop is Column F,row 5 and the third loop is Column F row 6
    and so on unitl the last row I= LR-That is where the code ends.
    I suspect you may need to use the Countif function in you code to test a range if it is zero- The same as you did with the sum function, but I am only guessing.

  13. #13
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    Hello, again.
    I am getting very frustrated with errors related to my new procedure SplitViolatorTYPES.
    It's an error starting in the If_ElseIf part. I've edited it various ways and tried to research it in my manual but need help debugging. I've attached a practice file with the procedure in it: PRACTICE2.xlsm Could you help me fix this?

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    When I run your code it errors on this line. There is no worksheet with that name in this workbook. What exactly are you trying to do?

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  15. #15
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    Also your If else is inside a With Worksheet() end with. And looks like your missing the . before all Range object. See . highlighted in red below
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    SHOOOOT! I uploaded the wrong file. Here is the correct one: PRACTICE2b.xlsm
    I am trying to create three new worksheets from the WHISARD data that list all of the MW violators or OT violators or CL violators. I'm not doing that very well at the moment.

  17. #17
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    Try this

    Please Login or Register  to view this content.

  18. #18
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    Previously the If_ElseIF block was defined as in post#8 but it didn't work where I've placed it.
    I keep getting OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET.
    I tried declaring Dim LR as Range, then a few other things that probably show my ignorance so I wont mention.
    Does the IF block need to be outside the WITH block (before it perhaps?).

  19. #19
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    Can you tell me the Logic behide SplitViolatorTYPES. I dont see how you are splitting into different sheets. If you tell exactly what you trying to do I will code you up something.

  20. #20
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    I"m trying this now. Sent the last post before your last one arrived... will get back to everyone...

  21. #21
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    Oops. It happened again - our replies crossed over.
    In the file I sent you I"ve inserted rows that weren't there originally so it's not parsing the data correctly.
    It is, however, improving from last time!
    Before you spend time coding, let me work with it a bit more and get back to you with a positive result or need for followup. THank you!!

  22. #22
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    Wow. I'm able to create sheets with data on them, but it's sure not the data I want. I'm not sure what to do.
    Here's an updated file with procedure edits and results: PRACTICE2c.xlsm
    I am basically trying to filter the WHISARD TEST data for those rows that have a number <>0 in the MW violator column F (data copy to new wsMW), <>0 in the OT violator column G (data copy to new wsOT), and <>0 in the CL violator column H (data copy to new wsCL).
    If you have time/desire to assist, I'd be grateful.

  23. #23
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    Give this a try


    Please Login or Register  to view this content.
    Last edited by mike7952; 12-30-2012 at 11:05 PM.

  24. #24
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    Just in case you wanted NONviolators added

    Please Login or Register  to view this content.
    Last edited by mike7952; 12-30-2012 at 11:42 PM.

  25. #25
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    It appears to work great!!!
    Mike, you have opened up a whole new world for me! I studied your code and researched the parts I didn't quite understand (I've very little experience with arrays), and am very excited that you have provided me so much flexibility to add to this procedure as my project expands. LBound/Ubound seems extremely useful.
    Mike, as I was writing my response above you submitted another reply, so I've read it now also. Thank you for adding descriptions! I was actually going to ask you which part of the code was the equivalent of "paste" or "dumping" as the jargon goes. I'm so excited about this procedure because I know there will be other worksheets I'll want to create with different parts of the original data. Thanks for including the nonviolators page. I will create a violators sheet with your procedure also, instead of the one I have. I think yours is going to be much easier to tweak when I get the real database I'll be working with soon.
    I'm going to mark this thread solved, but will be studying it more in coming days. I hope it's OK if I check back with you if questions arise.
    Regards and Happy New Year!

  26. #26
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551

    Re: Explanation VBA FOR...NEXT loop

    Well heres code that will add the violators. Also added to Const for the columns so if columns change you only need to change them at the top of the code.

    Please Login or Register  to view this content.
    Last edited by mike7952; 12-31-2012 at 12:54 AM.

  27. #27
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,551
    Yeap no problem. If you start another thread just PM me the link to thread.

  28. #28
    Registered User
    Join Date
    12-23-2012
    Location
    Knoxville, TN
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Explanation VBA FOR...NEXT loop

    Very much appreciated. Enjoy the New Year celebrations...

+ 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