Identify and number Patterns

Re: Identify and number Patterns

kjbox
very good work around on the run time error 9
excellent work

Re: Identify and number Patterns

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

Sub PatternSearch() 
    Dim c00, sn, st 
    Dim j, jj As Long, dPRw As Double 
    Dim iAbv, iBlw As Integer


    If [N3] = vbNullString Or [N4] = vbNullString Then c01 ="You must enter a value (even if it is 0) for Above and Below" 
    if c01<>"" then goto XL90


    if application.countA(sheet1.cells(3,8).currentregion)<>sheet1.cells(3,8).currentregion.cells.count then c01="Search criterion range not complete"
    if c01<>"" then goto XL90


    iAbv = [N3] 
    iBlw = [N4] 


    for each it in sheet1.cells(3,8).currentregion
       c00=c00 & it.value
    next


    Sheet2.Columns("L:Q").ClearContents
    dPRw=sheet1.cells(3,8).currentregion.rows.count    


    sn = Sheet1.Cells(1, 1).CurrentRegion 
    For j = 1 To UBound(sn) - dPRw 
        If sn(j, 2) = Val(Left(c00, 1)) Then 
            For jj = 0 To (dPRw * 4) - 1 
                If sn(j + jj \ 4, jj Mod 4 + 2) <> Val(Mid(c00, jj + 1, 1)) Then Exit For 
            Next 
            If jj = dPRw * 4 Then 
                Redim st(1 To (dPRw + iAbv + iBlw), 1 To 6) 
                For jj = 1 To (dPRw + iAbv + iBlw) * 6 
                    If j - iAbv + (jj - 1) \ UBound(st, 2) > UBound(sn) Then Exit For 
                    If j < iAbv And jj > UBound(sn, 2) * (dPRw + iBlw - 1 + j) Then Exit For 
                    st((jj - 1) \ UBound(st, 2) + 1, (jj - 1) Mod UBound(st, 2) + 1) =  sn(Application.Max(j - (iAbv + 1), 0) + (jj - 1) \ UBound(st, 2) + 1,  (jj - 1) Mod UBound(st, 2) + 1) 
                Next 
                Sheet2.Cells(Rows.Count, 12).End(xlUp).Offset(2).Resize(UBound(st), UBound(st, 2)) = st 
            End If 
        End If 
    Next 


    With Sheet2 
        .Columns("Q").AutoFit 
        .[L1] = "Search Results" 
    End With 


    If Sheet2.[L3] = vbNullString Then c01="No matching pattern found" 
    Application.Goto Sheet2.[L1] 


XL90:
    if c01<>"" then msgbox c01
End Sub

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:

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:

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

Cells(8,3).CurrentRegion

to

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

Re: Identify and number Patterns

Great work snb and KjBox, a very fast solution.

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

If Sheet2.[L3] = vbNullString Then 
  c01="No matching pattern found"
  c02="No Match"
  goto XL90 
end if


XL90:
  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.

Re: Identify and number Patterns

The thread that refuses to die.

KjBox - fairly sure snb was joking.

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

yes, better off to aviod "currentregion" and "Usedrange" with there quirks and the need for resume next

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

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