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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
only check 5.5
ASKER
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'?><SignalM
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
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'?><SignalM
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
Hi,
Opening a new question would be nice
Regards
Opening a new question would be nice
Regards
ASKER
This is the PERFECT solution.
I posted a follow-up question (problem extension) at: https://www.experts-exchange.com/questions/28911641/Macro-module-searching-for-message-content-follow-up-question.html
I posted a follow-up question (problem extension) at: https://www.experts-exchange.com/questions/28911641/Macro-module-searching-for-message-content-follow-up-question.html
ASKER
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