+ Reply to Thread
Results 1 to 9 of 9

Need explanation on how this macro works

  1. 06-09-2010, 01:50 PM #1
    rushtona is offline
    Registered User
    Join Date
    06-09-2010
    Location
    Port McNeill, BC, Canada
    MS-Off Ver
    Excel 2002
    Posts
    6

    Question Need explanation on how this macro works

    I have a macro that was writen a while back by a coworker that I need help understanding. I have attached a picture of the spreadsheet columns in question. The macro starts by looking in column 46 (AT) for the number 1 and then at some point changes the 1 to a 0. That's as much as I understand.

    Could someone please describe in detail what is happening in this macro, step by step. What the s and v and e are meant to do. If I understood this macro then I could possibly reproduce it in another file, which I'm hoping to do.
    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by Leith Ross; 06-09-2010 at 01:53 PM. Reason: Added Code Tags
    Reply With QuoteRegister To Reply
  2. 06-09-2010, 02:06 PM #2
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Need explanation on how this macro works

    Hello rushtona,

    The variables s, v, and e are used to track where the rows where a 1 is found and then ends. The assignments are "s" is the starting row, "e" the ending row, and "v" the cell value.

    The first loop starts at row 1. The loop continues down the rows until "v" = 1 is found. "s" is now the pointing to the next row. "s" is copied into "e". The second loop will now search for the row that has a zero. "e" will mark the end of the rows with a 1. This happens when "v" = 0.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  3. 06-09-2010, 05:02 PM #3
    rushtona is offline
    Registered User
    Join Date
    06-09-2010
    Location
    Port McNeill, BC, Canada
    MS-Off Ver
    Excel 2002
    Posts
    6

    Lightbulb Re: Need explanation on how this macro works

    Thanks Leith. I have included more of the macro than in my original post. I didn’t think this part was needed, but now that I’m starting to understand the macro I believe this is important to the outcome that I want.

    The code looks for all the cells that have a value of 1 and then copies these to the spreadsheet “email text”. It puts a 0 in column 44 beside the 1’s and then turns the 1 in column 46 to a 0 so that the data doesn’t get exported again.

    I know this is what the code does, but could you please explain the rows that I have question marks beside so I know what parts do what. Sorry if I haven’t asked the right questions. Trying to teach myself macro’s is going very slow.

    Thanks!

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  4. 06-09-2010, 05:25 PM #4
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Need explanation on how this macro works

    Hello rushtona,

    If you have a workbook, I would like to see it. Can you post it? It would make answering your questions much easier since we both would have the same information.
    Reply With QuoteRegister To Reply
  5. 06-09-2010, 05:55 PM #5
    rushtona is offline
    Registered User
    Join Date
    06-09-2010
    Location
    Port McNeill, BC, Canada
    MS-Off Ver
    Excel 2002
    Posts
    6

    Exclamation Re: Need explanation on how this macro works

    Here is a copy of the two spreadsheets. I had to copy the macro from the original workbook and now it's not working. I hope you can figure that out first before tackling the original question.

    I know what the macro is trying to do, but I don't understand what each line does.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 06-09-2010, 06:36 PM #6
    rushtona is offline
    Registered User
    Join Date
    06-09-2010
    Location
    Port McNeill, BC, Canada
    MS-Off Ver
    Excel 2002
    Posts
    6

    Re: Need explanation on how this macro works

    Here is another copy of the workbook with a macro that works.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 06-09-2010, 09:15 PM #7
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Need explanation on how this macro works

    Hello rushtona,

    Sorry for the delay. I had to make an unplanned trip. I rewrote the macro to make it easier to understand and read. In VBA it is rare that you need to select an object before performing an action on it. The original code was created using the macro recorder. Here is the revised macro with comments...
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-11-2010, 10:57 AM #8
    rushtona is offline
    Registered User
    Join Date
    06-09-2010
    Location
    Port McNeill, BC, Canada
    MS-Off Ver
    Excel 2002
    Posts
    6

    Re: Need explanation on how this macro works

    Hi Leith, the code that you provided above misses the last row of data that contains a 1 in column AT. The code picks up all the unexported data but misses the last row.

    I have meetings all day today so I won't be able to work on this again until Monday. Thanks for your help!
    Reply With QuoteRegister To Reply
  9. 08-23-2010, 10:57 AM #9
    rushtona is offline
    Registered User
    Join Date
    06-09-2010
    Location
    Port McNeill, BC, Canada
    MS-Off Ver
    Excel 2002
    Posts
    6

    Re: Need explanation on how this macro works

    I had to put this project on hold for a while, but now I'm back at it. The code needs a little tweaking so I'm going to talk my supervisor into letting me take a course on writing macros. That way I can fix it myself. Thanks for all you help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1