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!!
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.


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).Name = "SampleOutput"

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
        lngNR = lngNR + 1
        wsSO.Cells(lngNR, "A") = strResult
        strResult = ""
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)
           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:

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?

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.

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

only check 5.5
ExpExchHelpAnalystAuthor Commented:

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,

Opening a new question would be nice

ExpExchHelpAnalystAuthor Commented:
This is the PERFECT solution.    

I posted a follow-up question (problem extension) at:
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.