Re: [ExcelVBA] Re: specfic character pick

 

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@yahoo.com>
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 (20)

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

.

__,_._,___