Macro / module searching for "message content"

Hello Experts:

I kindly request your assistance in developing a macro (in VBA) that allows me to search through a spreadsheet that contains several tens of thousands of records.

As a picture is worth a thousands words, I have attached an example XLS with additional information.

I tried to keep the information and background generic and short.   Hopefully, the info in the XLS provides sufficient detail.   If not, please don't hesitate to ask for additional info.

 Thank you in advance!!
 EEH
Macro-Filter.xlsx
ExpExchHelpAnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try ( do not forget to tick the reference to MS VBS Regular esxpressions in Tools Reference)

Sub FindMessageTags()
Dim lngLastRowMP As Long
Dim lngLastRowData As Long
Dim lngRowMP As Long
Dim lngRowData As Long
Dim lngNR As Long
Dim wsDL As Worksheet
Dim wsSO As Worksheet
Dim wsData As Worksheet

Set wsData = Sheets("ExampleInput")
Set wsDL = Sheets("DynamicList")

On Error Resume Next
Set wsSO = Sheets("SampleOutput")
If Err.Number = 9 Then
    ' The sheet doesn't exist so create it
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    Sheets(Sheets.Count).Name = "SampleOutput"

Else
    wsSO.Cells.Clear
End If
On Error GoTo 0

lngLastRowDL = wsDL.Range("A1048576").End(xlUp).Row
lngLastRowData = wsData.Range("A1048576").End(xlUp).Row


With wsData
    For lngRowData = 1 To lngLastRowData
        For lngRowDL = 1 To lngLastRowDL
            regexRes = simpleRegex(.Cells(lngRowData, "A"), wsDL.Cells(lngRowDL, "A") & "[\s\S]*?>")
            If regexRes <> "" Then
                strResult = strResult & regexRes
            End If
        Next
        lngNR = lngNR + 1
        wsSO.Cells(lngNR, "A") = strResult
        strResult = ""
    Next
End With

End Sub
Private Function simpleRegex(myStr As String, strPattern As String) As String
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range


    If strPattern <> "" Then
        strInput = myStr
        
        With regEx
            .Global = True
            .MultiLine = False
            .IgnoreCase = True
            .Pattern = strPattern


            Set allMatches = regEx.Execute(strInput)
        End With
        If allMatches.Count <> 0 Then
            simpleRegex = allMatches.Item(0)
        Else
           simpleRegex = ""
        End If
    End If
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExpExchHelpAnalystAuthor Commented:
Rgonzo1971:

Thank you for your assistance in advance.   As you suggested, I checked the following two (2) references:
Microsoft VBScript Regular Expressions 1.0
Microsoft VBScript Regular Expressions 5.5

Once I execute the macro, the macro throws a compile error (i.e., "Method or data member not found) at the following line:
.MultiLine = False

How can this be fixed so that the macro executes?

EEH
ExpExchHelpAnalystAuthor Commented:
I commented the line out... it works fine w/o it.  

I will do little bit more analysis on this before I close the question.   At initial thought though, it looks great.   More to follow.

EEH
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Rgonzo1971Commented:
only check 5.5
ExpExchHelpAnalystAuthor Commented:
Rgonzo1971:

Based on the initial review, this process works absolutely great!

I realized, however, that I may need to tweak the process slightly.    If the additional modification deserves a separate question, I certainly can open one.   Please let me know.

Ok, right now, all messages (based on my provided examples), start with
"Received ABCD 2.0 message:"

What thinking about the concept earlier, I didn't think about that there are different messages types throughout the 60,000.   To keep it generic, let's assume for a moment that the different message types are simply differentiated by either "2.0", "3.0", or "4.0".   That said, the three messages types may read:

Received ABCD 2.0 message: <?xml version='3.0' encoding='ABC-1'?><SignalMessage rev='2.0'>
Received ABCD 3.0 message: <?xml version='3.0' encoding='ABC-1'?><Report time='10'>
Received ABCD 4.0 message: <?xml version='3.0' encoding='ABC-1'><Path count='1000'>

Please note that the above are only example (dummy) messages... all three message types have similar elements (e.g., ReportTime, PathCount, etc.).   However, there may be some slight differences amongst the three message types.

All that said, I would like to modify the code with some form of IF statement.   That is, ...
- if the message starts with message type "2.0", then I would like to apply the "filters" from "DynamicList" (column A)
- if the message starts with message type "3.0", then I would like to apply the "filters" from "DynamicList" (column B)
- if the message starts with message type "4.0", then I would like to apply the "filters" from "DynamicList" (column C)

Is that doable?   Again, if you prefer to have me open up a separate question (and close this one) please let me know.

Thank you in advance,
EEH
Rgonzo1971Commented:
Hi,

Opening a new question would be nice

Regards
ExpExchHelpAnalystAuthor Commented:
This is the PERFECT solution.    

I posted a follow-up question (problem extension) at:  http://www.experts-exchange.com/questions/28911641/Macro-module-searching-for-message-content-follow-up-question.html
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.