Paul, I agre wth everythnig you wriote, just mot so confident of finding them kywrds ;)
Dave G
--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> 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@...>
> 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