+ Reply to Thread
Results 1 to 5 of 5

Need to optimize the code

Hybrid View

  1. #1
    Registered User
    Join Date
    01-31-2018
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    17

    Need to optimize the code

    Below is my code, but when I execute it takes long time. I need to optimize it so that it takes less processing time.

    
    Sub RFQ()
    
    Dim j As Integer
    Dim irow As Integer
    Dim v As Integer
    Dim s As Integer
    Application.ScreenUpdating = False
    
    j = 3
    irow = 15
    s = 1
    v = Worksheets("MML").Cells(irow, 21).Value
     
    Do
        If v > 0 Then
            Worksheets("RFQ").Cells(j, 10).Value = Worksheets("MML").Cells(irow, 15).Value 'Qty 5
            Worksheets("RFQ").Cells(j, 9).Value = Worksheets("MML").Cells(irow, 14).Value 'Qty 4
            Worksheets("RFQ").Cells(j, 8).Value = Worksheets("MML").Cells(irow, 13).Value 'Qty 3
            Worksheets("RFQ").Cells(j, 7).Value = Worksheets("MML").Cells(irow, 12).Value 'Qty 2
            Worksheets("RFQ").Cells(j, 6).Value = Worksheets("MML").Cells(irow, 11).Value 'Qty 1
            Worksheets("RFQ").Cells(j, 5).Value = Worksheets("MML").Cells(irow, 10).Value 'Category
            Worksheets("RFQ").Cells(j, 4).Value = Worksheets("MML").Cells(irow, 9).Value 'Description
            Worksheets("RFQ").Cells(j, 3).Value = Worksheets("MML").Cells(irow, 8).Value 'P/n.
            Worksheets("RFQ").Cells(j, 2).Value = s                                            'sr. no.
            irow = irow + 1
            j = j + 1
            s = s + 1
            v = Worksheets("MML").Cells(irow, 11).Value
    
        Else
            irow = irow + 1
            v = Worksheets("MML").Cells(irow, 11).Value
        End If
     
     Loop Until IsEmpty(Worksheets("MML").Cells(irow, 11))
    
    
    End Sub

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,078

    Re: Need to optimize the code

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    01-31-2018
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    17

    Re: Need to optimize the code

    TMS-

    Thanks for reply, I have attached sample file for reference. There is button in RFQ Sheet. When you click that it gets data from MML sheet to here. The thing is if there is more data, then it takes longer time to execute.

    So I need to optimize the code.
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    10-02-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    1,222

    Re: Need to optimize the code

    If you are just moving data without any conditions or at least moving a block of cells/data this is the fastest method for doing so. See attached sample.

    Basically dump the source range to an array and then dump the array into the destination range.

    EDIT: It also doesnt help you have 38,534 styles in the workbook you attached. Presuming its based on your actual file you likely have the same issue in your file.

    Your used range in the MML sheet goes way beyond your data too. So you have excess cells.

    Also your module 2 code appears like it may not be very efficient though its not 100% clear what your doing, can you explain the purpose of the AddSheets routine and the logic behind it?

    You also use a table in MML sheet but do not use any listobject parts (rows, columns, etc) in your code.
    Attached Files Attached Files
    Last edited by Zer0Cool; 02-05-2018 at 01:20 PM.
    Ways to get help: Post clear questions, explain your overall goal, supply as much background as possible, respond to questions asked of you by those trying to help and post sample(s) files.

    "I am here to help, not do it for people" -Me

  5. #5
    Registered User
    Join Date
    01-31-2018
    Location
    New Jersey
    MS-Off Ver
    2016
    Posts
    17

    Re: Need to optimize the code

    Ok...In my case I have different sheets. So I have to fix stuff and then try it.

    Thanks

+ 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. Optimize a code
    By pezalmendra in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2015, 05:31 PM
  2. Optimize code
    By DarkKnightLupo in forum Excel Programming / VBA / Macros
    Replies: 22
    Last Post: 02-19-2014, 08:58 AM
  3. Optimize a slow code...
    By benoitly in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2013, 01:44 PM
  4. optimize the Code needs to run faster
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2012, 10:40 AM
  5. Optimize code
    By miso.dca in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 03:35 PM
  6. How can I optimize/simplify that code ?
    By Grek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2010, 03:38 PM
  7. Optimize VBA code
    By doodlebug in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2007, 07:53 AM

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