I don't think they're "errors".
I believe I recognize what he's trying to do.
I've done something like it before...
---------------------------------
You have a huge list of data that you need to separate into different
categories.
Perhaps to assign the record to a different individual.
(perhaps a list of "service tickets" that need to be assigned to different
departments by type of request: "hardware", "sofware", "application support",
etc.)
I have an "audit tool" that searches through hundreds of thousands of files in a
series of network folders
that look for "keywords" that are used to identify files that contain sensitive
information that may
be inappropriately placed in public file locations. If it finds a match, it
flags the file for a follow-up review.
---------------------------------
So, in the case of the list of text strings, you can read each record, searching
for a clue.
When you do this, what you're actually doing is searching for "keywords".
So instead, load the specific keywords into the Dictionary Object.
I would do this by listing the keywords (and Classifications) on a separate
sheet.
And use a function to load these values at run-time.
That way, a new keyword combination can be added very easily.
The macro then does a "rough" categorization of the records.
Some records may not have any matching keywords, which will have to be handled
manually.
However, during the manual process, if a pattern emerges that suggests adding a
keyword...
By using the "split" function on the text string, then removing punctuation,
he will be effectively searching for a "whole word" match instead of a "partial"
match
(which is what he seems to require).
I'd also suggest forcing the words to "capital" (ucase) when loading the
dictionary object
as well as splitting the string in order to do case-independent comparisons.
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------
________________________________
From: dmgathmann <dmgathmann@yahoo.com>
To: ExcelVBA@yahoogroups.com
Sent: Fri, August 24, 2012 8:30:46 AM
Subject: [ExcelVBA] Re: specfic character pick
Agreed, Paul. But, wouldn't he'd still have to go through them to find the
errors so as to add them to the dictionary?
Dave G
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> Since he's using keywords to "classify" the record,
> he could create a dictionary object and store the classification for each
> keyword.
>
> Then, split the sentence and check each word against the dictionary.
> The first one found establishes the Classification, then breaks out of the
>loop.
>
> That way, the keyword definitions are expandable.
>
> Paul
> -----------------------------------------
> "Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can." - John Wesley
> -----------------------------------------
>
>
>
>
> ________________________________
> From: dmgathmann <dmgathmann@...>
> To: ExcelVBA@yahoogroups.com
> Sent: Thu, August 23, 2012 11:46:04 AM
> Subject: [ExcelVBA] Re: specfic character pick
>
>
> This would appear to me to be quite involved to correctly capture all keyword
> instances from free flowing accounts/verbiage. I'd think that you'd have to
>take
>
> into consideration keyword misspellings, truncations, and the like. Having said
>
> this, I offer a modification to that of Paul Schreiner's
>
> Sub FindKeys()
> 'cell(1,"A") contains: the quick brown fox jumped over the lazy dogs
> Dim TheText As String
> Dim i As Integer, p As Integer
> Dim r As Long
> Dim Keys(1 To 4) As String
> Dim IsGood As Boolean, BoEOF As Boolean
>
> Keys(1) = "the"
> Keys(2) = "jump"
> Keys(3) = "jumped"
> Keys(4) = "dogs"
>
> On Error GoTo erout
> For r = 1 To 10
> If Cells(r, "A") <> Empty Then
> TheText = Cells(r, "A")
> For i = 1 To UBound(Keys)
> IsGood = True
> p = InStr(TheText, Keys(i))
> If p <> 0 Then
> If Asc(UCase(Mid(TheText, p - 1, 1))) >= 65 And _
> Asc(UCase(Mid(TheText, p - 1, 1))) <= 90 Then
> If Not BoEOF Then IsGood = False
> BoEOF = False
> End If
> If Asc(UCase(Mid(TheText, p + Len(Keys(i)), 1))) >= 65 And _
> Asc(UCase(Mid(TheText, p - 1, 1))) < 90 Then
> If Not BoEOF Then IsGood = False
> BoEOF = False
> End If
> If IsGood Then
> MsgBox Keys(i) & " located at " & p
> Else
> MsgBox Keys(i) & " not located"
> End If
> End If
> Next i
> End If
> Next r
> Exit Sub
> erout:
> BoEOF = True
> Resume Next
> End Sub
>
> Dave G
>
> --- In ExcelVBA@yahoogroups.com, Tayyab <sheikhtayyab@> wrote:
> >
> > the main problem is I received different data every time, Let me tell you the
>
> >problem,
> > I received expenses accounting transactions on every month, I analysis these
> >transactions through their narrations, In narrations different style of
> >sentences mentioned . I have to manually marked specific tag to each
> >transactions like Maintenance , Provision etc. so do the work manually and
>spend
>
> >1 to 2 hours I am try to automate this sort of marking, and finally I am
>not
>
> >professional programmer of Excel, I am accounting guy, so your help and
>comments
>
> >really help me a lot.
> > thanks and regards
> > tayyab
> >
> >
> >
> > ________________________________
> > From: "1z@" <1z@>
> > To: ExcelVBA@yahoogroups.com
> > Sent: Wednesday, August 22, 2012 6:07 PM
> > Subject: Re: [ExcelVBA] Re: specfic character pick
> >
> >
> > Â
> >
> > Yeah....
> >
> > I've had my own problems with InStr. You really have to be sure what your
>data
>
> >is going to look like... and it has no exceptions either like ignoring stuff
>you
>
> >don't want to look at like items in quotes or brackets or whatever.
> >
> > I've written various versions of my own and used stuff from Chip Pearsons
>site
>
> >as well.
> >
> > For this problem.. given a sentence... I'd suggest removing punctuation,
>Split
>
> >to get the words and loop through the array to test for specific things using
> >StrComp with an option to ignore case if needed. It won't be quick though.. I
> >think Split is relatively slow.
> >
> > Lisa
> >
> > Sent: Wed, Aug 22, 2012 2:42 pm
> > Subject: [ExcelVBA] Re: specfic character pick
> >
> > I agree that InStr is working. I'm thinking that for each find of the root
> >word,
> >
> > a look at the chars immediately proceeding and following the find, while
> > checking for end of file and "before the file" (don't know what the common
> > terminology for that is), will be required.
> >
> > Dave G
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar