[ExcelVBA] Re: specfic character pick

 

On second thought, I suppose one could rely on an internal check to make sure all dollars have been accounted for: sum($$) in report vs sum($$ classified). Then only check those that don't match. This could save time.

Dave G

--- In ExcelVBA@yahoogroups.com, "dmgathmann" <dmgathmann@...> wrote:
>
> 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]
> >
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (22)

Recent Activity:

• New Members 5

Visit Your Group

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

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___