Identify and number Patterns

  • Re: Identify and number Patterns


    some comments:
    Adding a blank line between each step can improve the readability of the code considerably.


  • Re: Identify and number Patterns


    Some comments:
    Neat reductions of my code, but using CurrentRegion for the search criterion when loading c00 and as a check that no criteria is missing does not work because of the headers. Also:

    Code
    1. If Sheet2.[L3] = vbNullString Then c01="No matching pattern found", , "No Match"


    gave a 'compile error, end of statement expected'. That was due to the quotation marks, needed for msgbox, within the string, trying to get it to work correctly with the quotation marks right for msgbox to show message and title was driving me nuts! So I deleted the message title:

    Code
    1. If Sheet2.[L3] = vbNullString Then c01 = "No matching pattern found"


    I changed the sheet, giving a blank row between headers and pattern criteria start, and given those 4 cells data validation to ensure they remain blank, then changed each instances of

    Code
    1. Cells(8,3).CurrentRegion


    to

    Code
    1. Cells(8,4).CurrentRegion


    then the code ran perfectly.


    The amended file with the new code is here:


    https://www.dropbox.com/s/vpuj…_Search_Using_Arrays2.zip


    and the original:


    https://www.dropbox.com/s/06gp…n_Search_Using_Arrays.zip

    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: Identify and number Patterns


    Of course, -- c01="No matching pattern found", , "No Match" -- gives an error.
    That's why I didn't use it


    You could consider


    Code
    1. If Sheet2.[L3] = vbNullString Then
    2. c01="No matching pattern found"
    3. c02="No Match"
    4. goto XL90
    5. end if
    6. XL90:
    7. if c01<>"" then msgbox c01,,,c02


    Regarding the structure of the file:


    'Stucturing precedes coding'
    A carefully built sheet can save a considerable amount of code.

  • Re: Identify and number Patterns


    Quote

    Of course, -- c01="No matching pattern found", , "No Match" -- gives an erro.
    That's why I didn't use it


    See your code in post #222, I think you will find you did use it.

    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: Identify and number Patterns


    arr man , ok
    Sometimes Excel cannot properly calculate what the used range or current region is. Some data may have been cleared, however, Excel still thinks that those cells are being used.they are quirky at best so "On Error Resume Next" is need because if there are no any matches, the code will error. ther are better longer ways to ensure no errors


    Using "goto" to redirect to another line is spaghetti code and should be replaced with if structure


    dont be in a mad rush to reduce the code! Its code so it doesnt matter how long it is.. make it easy to read ,debug and modify..

  • Re: Identify and number Patterns


    KjBox,


    On that one I was mistaken. For the other c01 's I had removed the title string., I had overlooked the last one.


    StephenR


    Thanks for being a 'witness a décharge'...


    pike,


    Basically 'On error Resume Next' is a goto statement too. ;)

  • Re: Identify and number Patterns


    All I know is I LOVE IT and appreciate ALL in working hard to make my job easier. I was trying not to post Pike and let it die but since it took another breath I thought I would chime in to let EVERYONE know that the last upgrade called Pattern_Search_Using_Arrays.xlsm is the bomb. It does more than I expected and could ever want. so THANK YOU THANK YOU THANK YOU. Now if i an only get in my house and unlock my doors I will be back in biz....

  • Re: Identify and number Patterns


    larbec larbec larbec your a unit .. hope you find the keys
    the modulus was the key to the zippy search very clever way to asses the data in the array and the pattern

    Code
    1. If sn(j, 2) = Val(Left(c00, 1)) Then
    2. For jj = 0 To (dPRw * 4) - 1
    3. If sn(j + jj \ 4, jj Mod 4 + 2) <> Val(Mid(c00, jj + 1, 1)) Then Exit For
    4. Next