How to run vba faster with this code

  • code is working in few data but when I try to load it with 15,000 lines in excel it freeze and not responding
    hope you can assist me on this.


    IF POSSIBLE TO ADD PROGRESS BAR


  • Re: How to run vba faster with this code


    Can you please post the file this code works on? There is a lot that can be done just cleaning up this code so that it will be faster. Never mind the 15k rows. Lets clean the macro first

  • Re: How to run vba faster with this code


    thanks for the reply this a report that identify randomly i need to clena the code

  • Re: How to run vba faster with this code


    @ dbestal


    Please do not repeatedly bump your thread up, especially after members have already replied to you. Try answering the questions you have been asked.


    If you continue to bump the thread expect it to be closed.


    Attach a sample workbook, no need for all 15,000 rows - about 20 will suffice. Manually create the Report sheet to show the result you are trying to achieve.


    [sw]*[/sw]

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: How to run vba faster with this code


    Hi dbestal - Some thoughts after having looked through your file. My first impression is that you can do most of what you want to do with vlookups and a pivot table.


    The vlookups - You look through the field name looking for key words. If there is a match on them, then Critical, else Non-Critical. All you would need to do would be to add another tab with the words to look for and use formulas to return Critical or Non-Critical.


    Pivot tables will return exactly the look you want. Concerning the count, you would have to add Vendor Number to the values section of the pivot table to get a count but you can get it.


    General comments about the code. Much of your code exists just to rearrange the data tab. Unless you had some specific requirement that the data tab must look some way, then you would be better off using the find function to find the column headers you want to find and work with them in memory instead of physically re-arranging your data. Also, I get why you color code (so that you can identify Critical if one color and Non-Critical if another color) but there is no need to do that either. You have options.


    1) Store the words to find on another tab and reference that table in code
    2) Use an array or a a dictionary. I am partial to the dictionary because I don't have to worry about its size and the .exists function is clutch.


    If the pivot table wont suite your needs for some reason, please let me know and I will help on the code.

  • Re: How to run vba faster with this code


    thanks for the reply actually the user prefer macro, appreciate your assistance on this can you send me a file for your suggested action I will compare if it will capture the same result :)


    I need to add also that the fieldname has multiple category per one vendor lookup will not be able to capture.

  • Re: How to run vba faster with this code


    "I need to add also that the fieldname has multiple category per one vendor lookup will not be able to capture."


    Can you post a revised sample showing this?

  • Re: How to run vba faster with this code


    please assist how to make this line dynamic reference to another sheet list of values



    Code
    If Trim(ActiveCell.Offset(i, 0).Value) = "reference in sheet 2" then


    I have solved the vba not responding when huge data...

  • Re: How to run vba faster with this code


    Dynamic? What part should be dynamic? How should it be dynamic? Please provide as much background information that you can so that we can help.

  • Re: How to run vba faster with this code


    instead of locating the value like authorization etc.. in the code I want lookup those value in a new sheet


    Code
    Do Until i >= lastRow
    If Trim(ActiveCell.Offset(i, 0).Value) = "Authorization" Or Trim(ActiveCell.Offset(i, 0).Value) = "Bank Details" Or Trim(ActiveCell.Offset(i, 0).Value) = "Co.cde deletion flag" Or Trim(ActiveCell.Offset(i, 0).Value) = "Co.code post.block" Or Trim(ActiveCell.Offset(i, 0).Value) = "Company code data" Or Trim(ActiveCell.Offset(i, 0).Value) = "Deletion flag" Or Trim(ActiveCell.Offset(i, 0).Value) = "E-Mail Address" Or Trim(ActiveCell.Offset(i, 0).Value) = "Industry" Or Trim(ActiveCell.Offset(i, 0).Value) = "Payment methods" Or Trim(ActiveCell.Offset(i, 0).Value) = "Payt Terms" Or Trim(ActiveCell.Offset(i, 0).Value) = "Planning group" Or Trim(ActiveCell.Offset(i, 0).Value) = "Posting Block" Or Trim(ActiveCell.Offset(i, 0).Value) = "Tax Number 2" Or Trim(ActiveCell.Offset(i, 0).Value) = "W. Tax Code" Then
    ActiveCell.Offset(i, 0).Interior.Color = RGB(255, 0, 0)
    End If

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!