Is it Possible for excel to find patterns?

M

Mr Crandall

New Member
Joined
Nov 23, 2007
Messages
1
Hello,

Does anyone know how I can identify patterns in excel? For example:

Value of cell A1 is 11
Value of cell A2 is 15
Value of cell A3 is 17
Value of cell A4 is 11
Value of cell A5 is 15
Value of cell A6 is 17

Is it possible for it to identify 11,15,17 as a pattern?

Thanks,
Mr Crandall
 
Sort by date Sort by votes
Mr Crandall,

Welcome to the board.

Sample data:

Excel Workbook
ABC
111Search Pattern
21511
31715
41117
515
617
711
811
911
1015
1117
1211
1316
1417
1511
1615
1717
1811
1915
2017
2111
2211
2311
2415
2517
2611
2716
2817
Sheet1



After running the "FindPattern" macro:

Excel Workbook
ABC
111Search Pattern
21511
31715
41117
515
617
711
811
911
1015
1117
1211
1316
1417
1511
1615
1717
1811
1915
2017
2111
2211
2311
2415
2517
2611
2716
2817
Sheet1



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code: 
Option Explicit
Sub FindPattern()

    Dim lngLastRow, lngLoopCtr As Long
    Dim Ptrn1, Ptrn2, Ptrn3

    Ptrn1 = Cells(2, "C")
    Ptrn2 = Cells(3, "C")
    Ptrn3 = Cells(4, "C")

    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lngLastRow).Font.Bold = False

    For lngLoopCtr = 1 To lngLastRow Step 1
        If Cells(lngLoopCtr, "A") = Ptrn1 And Cells(lngLoopCtr + 1, "A") = Ptrn2 And Cells(lngLoopCtr + 2, "A") = Ptrn3 Then
            Range("A" & lngLoopCtr & ":A" & lngLoopCtr + 2).Font.Bold = True
        End If
    Next lngLoopCtr

End Sub


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Then run the "FindPattern" macro.

Have a great day,
Stan
 
Upvote 0
Hi,

I wondered if you can elaborate on this pattern finding rule and expand to find patterns across more than one column?
So in my example table I want to see if any of the patterns in Col1, Col2...etc are replicated anywhere else in any other column?
Highlightin the cell to identify the match would help, unless this can be done another way.
each "Col" is broken into 3 sperate columns

-- removed inline image ---
<table border="0" cellpadding="0" cellspacing="0" width="315"><colgroup><col style="mso-width-source:userset;mso-width-alt:768; width:16pt" span="15" width="21"> </colgroup><tbody><tr style="height:15.75pt" height="21"> <td colspan="3" class="xl70" style="height:15.75pt;width:48pt" height="21" width="63">Col 1</td> <td colspan="3" class="xl71" style="width:48pt" width="63">Col 2</td> <td colspan="3" class="xl84" style="width:48pt" width="63">Col 3</td> <td colspan="3" class="xl71" style="width:48pt" width="63">Col 4</td> <td colspan="3" class="xl84" style="border-right:1.5pt solid black; width:48pt" width="63">Col 5</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl74" style="height:15.0pt;border-top:none" align="right" height="20">1</td> <td class="xl75" style="border-top:none;border-left:none">
</td> <td class="xl82" style="border-top:none;border-left:none">
</td> <td class="xl87" style="border-top:none" align="right">1</td> <td class="xl75" style="border-top:none;border-left:none">
</td> <td class="xl88" style="border-top:none;border-left:none">
</td> <td class="xl85" style="border-top:none">
</td> <td class="xl75" style="border-top:none;border-left:none" align="right">2</td> <td class="xl82" style="border-top:none;border-left:none">
</td> <td class="xl87" style="border-top:none" align="right">1</td> <td class="xl75" style="border-top:none;border-left:none">
</td> <td class="xl88" style="border-top:none;border-left:none">
</td> <td class="xl85" style="border-top:none">
</td> <td class="xl75" style="border-top:none;border-left:none">
</td> <td class="xl76" style="border-top:none;border-left:none" align="right">3</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none" align="right">4</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">5</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" align="right" height="20">7</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none" align="right">7</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none" align="right">9</td> <td class="xl68" style="border-top:none" align="right">7</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">8</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none" align="right">12</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">11</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none" align="right">10</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none" align="right">12</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">11</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">14</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none" align="right">15</td> <td class="xl67" style="border-top:none" align="right">13</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">14</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none" align="right">13</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" align="right" height="20">16</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none" align="right">18</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">17</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none" align="right">16</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">17</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none" align="right">21</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none" align="right">21</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">20</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none" align="right">21</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">20</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" align="right" height="20">22</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none" align="right">22</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none" align="right">24</td> <td class="xl68" style="border-top:none" align="right">22</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none" align="right">22</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">26</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">26</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">25</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">26</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none" align="right">25</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl78" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl77" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none" align="right">30</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none" align="right">29</td> <td class="xl69" style="border-top:none;border-left:none">
</td> <td class="xl67" style="border-top:none" align="right">28</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl66" style="border-top:none;border-left:none">
</td> <td class="xl68" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl69" style="border-top:none;border-left:none" align="right">30</td> <td class="xl67" style="border-top:none">
</td> <td class="xl65" style="border-top:none;border-left:none">
</td> <td class="xl78" style="border-top:none;border-left:none" align="right">30</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl79" style="height:15.75pt;border-top:none" height="21">
</td> <td class="xl80" style="border-top:none;border-left:none" align="right">32</td> <td class="xl83" style="border-top:none;border-left:none">
</td> <td class="xl89" style="border-top:none" align="right">31</td> <td class="xl80" style="border-top:none;border-left:none">
</td> <td class="xl90" style="border-top:none;border-left:none">
</td> <td class="xl86" style="border-top:none">
</td> <td class="xl80" style="border-top:none;border-left:none">
</td> <td class="xl83" style="border-top:none;border-left:none" align="right">33</td> <td class="xl89" style="border-top:none">
</td> <td class="xl80" style="border-top:none;border-left:none" align="right">32</td> <td class="xl90" style="border-top:none;border-left:none">
</td> <td class="xl86" style="border-top:none">
</td> <td class="xl80" style="border-top:none;border-left:none">
</td> <td class="xl81" style="border-top:none;border-left:none" align="right">33</td> </tr> </tbody></table>

-- removed inline image ---
 
Last edited:
Upvote 0
Hi there,

Your code worked very nicely for me. Thank you for posting it. I was wondering if there is a way to give me the count of the patterns in a separate column?

Cheers,

Nina
 
Upvote 0
Hi there,

Your code worked very nicely for me. Thank you for posting it. I was wondering if there is a way to give me the count of the patterns in a separate column?

Cheers,

Nina







stanleydgromjr said:
Mr Crandall,

Welcome to the board.

Sample data:

Sheet1

*ABC
*Search Pattern
*
*
*
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:95px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: right"]17[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


After running the "FindPattern" macro:

Sheet1

*ABC
*Search Pattern
*
*
*
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**
**

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:95px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]11[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: right"]17[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code: 
Option Explicit
Sub FindPattern()

    Dim lngLastRow, lngLoopCtr As Long
    Dim Ptrn1, Ptrn2, Ptrn3

    Ptrn1 = Cells(2, "C")
    Ptrn2 = Cells(3, "C")
    Ptrn3 = Cells(4, "C")

    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lngLastRow).Font.Bold = False

    For lngLoopCtr = 1 To lngLastRow Step 1
        If Cells(lngLoopCtr, "A") = Ptrn1 And Cells(lngLoopCtr + 1, "A") = Ptrn2 And Cells(lngLoopCtr + 2, "A") = Ptrn3 Then
            Range("A" & lngLoopCtr & ":A" & lngLoopCtr + 2).Font.Bold = True
        End If
    Next lngLoopCtr

End Sub


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Then run the "FindPattern" macro.

Have a great day,
Stan
Click to expand...
 
Upvote 0
Re: Is it Possible for excel to find average of a patterns?

hi,
Does anyone know how I can identify average of a patterns in excel ?

for example


[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A[/TD]
[TD]Col B[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]66[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]t[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]

how can find average ColB when from pattern (f,t) in ColA

in this example i must 3 output as :
1-average(5,7)

2-average(6,3)

3-average(15,11)

thanks.
 
Upvote 0
Hi, thank you for the code. It was so helpful!

How can I edit the code to search for patterns of different lengths? For example, I want to find the pattern "9,10,11,12," which has 4 values.
 
Upvote 0
stanleydgromjr said:
Mr Crandall,

Welcome to the board.

Sample data:

Excel Workbook
ABC
111Search Pattern
21511
31715
41117
515
617
711
811
911
1015
1117
1211
1316
1417
1511
1615
1717
1811
1915
2017
2111
2211
2311
2415
2517
2611
2716
2817
Sheet1



After running the "FindPattern" macro:

Excel Workbook
ABC
111Search Pattern
21511
31715
41117
515
617
711
811
911
1015
1117
1211
1316
1417
1511
1615
1717
1811
1915
2017
2111
2211
2311
2415
2517
2611
2716
2817
Sheet1



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code: 
Option Explicit
Sub FindPattern()

    Dim lngLastRow, lngLoopCtr As Long
    Dim Ptrn1, Ptrn2, Ptrn3

    Ptrn1 = Cells(2, "C")
    Ptrn2 = Cells(3, "C")
    Ptrn3 = Cells(4, "C")

    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lngLastRow).Font.Bold = False

    For lngLoopCtr = 1 To lngLastRow Step 1
        If Cells(lngLoopCtr, "A") = Ptrn1 And Cells(lngLoopCtr + 1, "A") = Ptrn2 And Cells(lngLoopCtr + 2, "A") = Ptrn3 Then
            Range("A" & lngLoopCtr & ":A" & lngLoopCtr + 2).Font.Bold = True
        End If
    Next lngLoopCtr

End Sub


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Then run the "FindPattern" macro.

Have a great day,
Stan
Click to expand...
Thank you so much, friend. This helped me a lot.
 
Upvote 0
stanleydgromjr said:
Mr Crandall,

Welcome to the board.

Sample data:

Excel Workbook
ABC
111Search Pattern
21511
31715
41117
515
617
711
811
911
1015
1117
1211
1316
1417
1511
1615
1717
1811
1915
2017
2111
2211
2311
2415
2517
2611
2716
2817
Sheet1



After running the "FindPattern" macro:

Excel Workbook
ABC
111Search Pattern
21511
31715
41117
515
617
711
811
911
1015
1117
1211
1316
1417
1511
1615
1717
1811
1915
2017
2111
2211
2311
2415
2517
2611
2716
2817
Sheet1



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code: 
Option Explicit
Sub FindPattern()

    Dim lngLastRow, lngLoopCtr As Long
    Dim Ptrn1, Ptrn2, Ptrn3

    Ptrn1 = Cells(2, "C")
    Ptrn2 = Cells(3, "C")
    Ptrn3 = Cells(4, "C")

    lngLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:A" & lngLastRow).Font.Bold = False

    For lngLoopCtr = 1 To lngLastRow Step 1
        If Cells(lngLoopCtr, "A") = Ptrn1 And Cells(lngLoopCtr + 1, "A") = Ptrn2 And Cells(lngLoopCtr + 2, "A") = Ptrn3 Then
            Range("A" & lngLoopCtr & ":A" & lngLoopCtr + 2).Font.Bold = True
        End If
    Next lngLoopCtr

End Sub


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Then run the "FindPattern" macro.

Have a great day,
Stan
Click to expand...
One question. how to put more than one cell data in the pattern. Like Ptrn2 = Cells(3,4,5,6,7, "C")? Is it doable in some way?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA search loop
Replies
6
Views
196
DanteAmor
DanteAmor
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
340
dallas2699
D
E
  • Question Question
auto fill yes/no from different sheet
Replies
1
Views
71
Joe4
Joe4
S
  • Solved
change macro that changes interior color
Replies
2
Views
160
still learning
S
S
  • Solved
Find value in a variable range
Replies
2
Views
112
susom
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top