-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Searching Comments
- Thread starter tedc
- Start date
Justinlabenne
Well-known Member
- Joined
- Jun 12, 2004
- Messages
- 957
What do you want to do with the extracted info? Where should it be going or how do you want to see it?
This can be done easily with vba, but there is a multitude of ways it can be extracted, do you want to see the results go into a worksheet, on at a time in a message box, etc...
This can be done easily with vba, but there is a multitude of ways it can be extracted, do you want to see the results go into a worksheet, on at a time in a message box, etc...
Upvote
0
PA HS Teacher
Well-known Member
- Joined
- Jul 17, 2004
- Messages
- 2,838
Here is some code I use to search all comments in columns 1, 2, and 8 for the search terms in Cell I1. If the search terms are present, it puts a true in column 9, otherwise it puts a false. It then uses autofilter to filter out the falses.
In addition to filtering, the code also calls a sub HighlightSearchTerms, that highlights that particular word in the comments. Let me know if you are interested in this code as well.
You could probably adjust this code to meet your needs, but Justin is right, you need to explain what you'd like to extract, and how you'd like that info displayed, comment box, written to a sheet etc.
In addition to filtering, the code also calls a sub HighlightSearchTerms, that highlights that particular word in the comments. Let me know if you are interested in this code as well.
Code:
' ************** Query? Search of Comments? ***********************************************************
Sub APFilter()
Application.EnableEvents = False
Selection.AutoFilter
Dim sCriteria As String, iNumWords As Integer, iMatch As Integer
Dim bMatch As Boolean, i As Integer, j As Integer, k As Integer
Dim iMatch1 As Integer, iMatch2 As Integer, iMatch8 As Integer
Dim sWord() As String
' ****** Pulls in Search Criteria *****************************************************
sCriteria = Application.WorksheetFunction.Trim(Range("I1"))
iNumWords = 1
iWord = 1
For i = 1 To Len(sCriteria)
If Asc(Mid(sCriteria, i, 1)) < 33 Then
iNumWords = iNumWords + 1
End If
Next i
ReDim sWord(1 To iNumWords)
For i = 1 To Len(sCriteria)
If Asc(Mid(sCriteria, i, 1)) > 33 Then
sWord(iWord) = sWord(iWord) & Mid(sCriteria, i, 1)
Else
iWord = iWord + 1
End If
Next i
' ************************* Check for Matches and write True/False to Sheet **************************
For k = 2 To Cells(65536, 2).End(xlUp).Row
bMatch = True
For i = 1 To iNumWords ' For each word in Cell I1
If sWord(i) = "And" Or sWord(i) = "and" Or sWord(i) = "&" Or sWord(i) = "or" Then GoTo SkipForAndOR 'don't treat the booleans(and/or) words as on of your search criteria
' iMatch is 0 if the current problem does not contain sWord(i) within the values contained in the following columns.
iMatch = InStr(1, Cells(k, 1) & Cells(k, 2) & Cells(k, 3) & Cells(k, 8), sWord(i), vbTextCompare)
iMatch1 = InStr(1, Cells(k, 1), sWord(i), vbTextCompare)
iMatch2 = InStr(1, Cells(k, 2), sWord(i), vbTextCompare)
iMatch8 = InStr(1, Cells(k, 8), sWord(i), vbTextCompare)
If iMatch1 > 0 Then Call HighlightSearchTerms(k, 1, Cells(k, 1).Comment.Text, sWord(i))
If iMatch2 > 0 Then Call HighlightSearchTerms(k, 2, Cells(k, 2).Comment.Text, sWord(i))
If iMatch8 > 0 Then Call HighlightSearchTerms(k, 8, Cells(k, 8).Comment.Text, sWord(i))
' Handles the logic of the search terms
If i > 1 Then
If sWord(i - 1) = "And" Or sWord(i - 1) = "and" Then
bMatch = bMatch And iMatch <> 0
ElseIf sWord(i - 1) = "Or" Or sWord(i - 1) = "or" Then
bMatch = bMatch Or iMatch <> 0
GoTo SkipByOrCondition
Else
bMatch = bMatch And iMatch <> 0
End If
Else
bMatch = bMatch And iMatch <> 0 'For the First Word
End If
SkipForAndOR:
Next i
SkipByOrCondition:
Cells(k, 9).Value = bMatch
Next k
Application.EnableEvents = True
End SubYou could probably adjust this code to meet your needs, but Justin is right, you need to explain what you'd like to extract, and how you'd like that info displayed, comment box, written to a sheet etc.
Upvote
0
