+ Reply to Thread
Results 1 to 3 of 3

Optimize macro by avoiding using for statements

  1. #1
    Registered User
    Join Date
    10-22-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    15

    Optimize macro by avoiding using for statements

    Hello everyone, I have read that using for statements makes a macro slow. I found this to be true because in my worksheet, I want to work with a large database in excel and I use for to loop through it. When the number of rows is big, it can take up to 15min. Anyone can suggest me better ways to do this?

    The following part of the code tells me where the data starts and where it ends in the specifed sheet:
    Please Login or Register  to view this content.
    after I get from where my range starts and where it ends, I work with it. The following code is just an example of what I want to do just to give you the idea

    Please Login or Register  to view this content.
    there's more code between that, but the goal is to start with the first data in range N&x, from there, search in the rest of the range until "q" for cells that match the data in range N&x. The complete code is the following for more details:

    Please Login or Register  to view this content.

    I hope everything is clear, i'm pretty sure there are better ways to write that code but i'm new in programming, also the code above only has the essential things id like to improve, which are the for staments.
    Last edited by alexholly; 12-15-2015 at 01:05 AM.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    Re: Optimize macro by avoiding using for statements

    I'm sorry it's not really clear what you want to do.

    For example in the first piece of code what are celdainicial and celdafinal supposed to refer to/represent?

    Also in the 2nd/3rd pieces of code what are you trying to do?

    Any chance you could upload a sample workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    10-22-2015
    Location
    Mexico
    MS-Off Ver
    2013
    Posts
    15

    Re: Optimize macro by avoiding using for statements

    Hello Norie, sorry I was not clear. Im sort of new with this programming stuff and I find it hard to express what I want to do. Celdainicial refers to inital cell, where the range starts, and celdafinal where the range ends. Also the Word "mes" refers to month in spanish. When you run the macros, you'll be asked to select months, so you got to put them im spanish. You can try with Noviembre or Diciembre or both for the button reporte de and 2015 as year (aņo operativo).
    The 2nd part of code I tried to simplify the most important thing I want to do in the 3rd, that is basically taking the value of cell X, then compare this value with the rest of the range and store the values that match with the value of cell X. This steps for everycell.
    You can find the workbook attached
    I'm 100% sure that all the macros in the workbook can be optimized, I would appreaciate a lot your suggestions, not only for the For Staments.
    Thanks a lot and have a great day!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Avoiding Nested If statements
    By TimEd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2012, 09:11 PM
  2. Replies: 4
    Last Post: 09-26-2012, 01:15 AM
  3. Need to optimize the small macro
    By Carnifex930 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-11-2011, 09:00 PM
  4. Avoiding "If" statements in macro
    By Myrmecophaga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2008, 03:23 AM
  5. [SOLVED] Optimize simple macro
    By Biff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-06-2005, 01:05 AM
  6. Optimize simple macro
    By Biff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-05-2005, 11:05 PM
  7. [SOLVED] Optimize simple macro
    By Biff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-05-2005, 10:05 PM
  8. [SOLVED] Optimize simple macro
    By Biff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-01-2005, 09:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1