Posts by bryce

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    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 find and replace multiple values at once in Excel based on date

    Your revised sample has duplicate entries. There is no way for me to know which of these three items should be used as a replacement value. For instance, in columns A & B, there are 3 instances of 2/21/2017 & container B. There is no information for me to know which of the values in columns E through H are a match. I will need extra information to provide a match.

    DATE mention cell Replace Range Time
    2/21/2017 container B GRAPE345 10:00
    2/21/2017 container B APPLLE678 11:00
    2/21/2017 container B GRAPE345 13:00

    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: Using INDEX MATCH functions via VBA

    Lol...Carim. I just found your worksheet change event. For a second I was blown away that you managed to get 2 lists to return the corresponding matching value with nothing but 2 named ranges:)

    Re: How to find and replace multiple values at once in Excel based on date

    If I understand, you want to replace the values in column B with values from column G, if A&B = E&F. Is that right?

    Give this a shot.

    Re: Counting appearance of non-input and else count multiple input

    Hi Spoons. You can give this a shot.

    Re: Copy Rows from Sheet 2 to Sheet 1 based on column values

    For me, it is not clear where this row would be inserted. Sheet2 might have a parent ID of 2 in 2 rows but Sheet1 doesn't have a parent ID. The only common field between Sheet1 and Sheet2 is the index which is unique on both sheets, there are no repeats.

    Maybe it would be easier to help if you told us more about what the goal is. What is the purpose of combining sheets?