Posts by Justin Doward

    Hi Glytch,


    I do not think you need the match component of this code so you could just replace it with the appropriate column references.


    Note the code goes in the sheet module not in a new module.

    Hi SA,


    I would use the code as follows:


    The application.calculation lines can be left out if there are no formula, or not large numbers of formula, in your sheet.

    Depending on what the values are in the cells being copied it may be preferable to use .value2 rather than .value, if there are dates and currency I think .value can cause issues. You can also change to .Text, but I do not think there is any gain.


    Depending on the purpose of what you are doing there may be more efficient ways of achieving the goal which might be why Roy is asking the question, this is just how I would approach your specific code (though there always seems to be a more efficient approach out there:)


    Justin

    No Problem,

    Note that the destination range for the autofill needs to start at th e column you wish to fill from, and end at the final column you wish to fill, so in the case you posted it will start in the May column (from row 5 to 123) then copy to the June column in the same row numbers.


    cheers


    Justin

    Hello DE,


    Try this:


    Code
    1. Sub AutoFill()
    2. Dim ws3 As Worksheet: Set ws3 = Sheet12
    3. ws3.Select
    4. Cells.Find(What:=Range("PreviousMonth").Value, LookIn:=xlValues).Offset(1, 0).Range("A1").Select
    5. Range(Selection, Selection.End(xlDown)).Select
    6. Selection.AutoFill Destination:=Range(ws3.Cells(5, Selection.Column), ws3.Cells(123, Selection.Column + 1)), Type:=xlFillDefault
    7. End Sub

    Your first method works fine for me, I tested as follows with the debug.print window showing 5 then 4 as expected and the watch window for the array losing "purple" after the redim. Not sure why it is not working for you:


    Hi GV,


    This was the best permutation without repetition code I could find:

    https://stackoverflow.com/ques…on-without-repetition-vba


    Modified to:


    Solves the first part of your problem.


    I do not fully understand the code yet, so would not currently be able to build in the adversity check... I will have another look later and post if I solve it.


    Justin

    HI RR,


    This code works but starts on sheet 2 at B1, you can change to start pasting in A1 with an if function if necessary. To test it I changed your if function in A1 to read =if(C11 > C10, TRUE, "") then put a number in C10 that would cause some false results.


    Hi Pete,


    copy all of the words into the cell and use this macro to sort and remove duplicates:


    Thank you Ali, my fault taking it off line I thought we could put the pertinent updates here and avoid having pages and pages of chit-chat and minor updates to the puzzle.


    I thought I had checked for leading and trailing spaces by retyping in the S in those locations, it must have registered no change when I typed in the S but did register a change to the value when I typed in a different letter (hence other words working but the SAS and SNOOP not).

    I think the code should look something like this:



    I see how the loop could work now assuming it will always reach a zero value, but it has the potential to be infinite so I think should be replaced. If you cannot get the above to work upload an example sheet and I will have a look.

    Hi Rookie,


    Don't forget to read the rules and wrap your code.


    The loop you have:

    Code
    1. Do While WorksheetFunction.CountA(Workflow.Range(Cells(R, C1), Cells(R, C2))) <> 0
    2. R = R + 1
    3. Loop

    is infinite once initiated, there is nothing within the loop to allow it to stop if the range is not = 0 at the start.


    i would recommend having a look at "select case" rather than the series of else if statements you are using (https://docs.microsoft.com/en-…elp/select-case-statement)


    There is also no reason to select any range in your code.

    Hi Pete,


    I am working on a solution for this that does not involve a dictionary, just uses the list of words already on the page which I should have thought of in the first place. Although it should be simple I have not got the code to work correctly yet.

    Make sure you include the cross posting at excelforum in your question, it prevents people from wasting their time and keeps you in the good books with the moderators:


    Cross Post is at:

    MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    https://www.excelforum.com/exc…matching.html#post5339488


    Regards

    Justin