Greetings
I think that Tayyab wants a user Defined Function like this :-
Public Function MatchWordInSentence(ByVal vSentence As Variant) As Variant
Dim nCharacterPosition As Long, vSplitSentence As Variant, vMatchList As Variant
Dim nOuterIndex As Long, nInnerIndex As Long
Const SPACE = " "
vMatchList = Array("lazy", "jumps", "over")
vSentence = LCase(vSentence) ' hence ByVal
For nCharacterPosition = 1 To Len(vSentence)
Select Case Mid(vSentence, nCharacterPosition, 1)
Case "a" To "z", "1" To "0"
Case Else
Mid(vSentence, nCharacterPosition, 1) = SPACE
''''
End Select
Next nCharacterPosition
vSplitSentence = Split(Application.WorksheetFunction.Trim(vSentence), SPACE) ' this Trim removes internal multiple spaces
For nOuterIndex = 0 To UBound(vSplitSentence) ' zero base
For nInnerIndex = 0 To UBound(vMatchList) ' zero base
If vSplitSentence(nOuterIndex) = vMatchList(nInnerIndex) Then
MatchWordInSentence = vMatchList(nInnerIndex)
Exit Function ' thus first match only
End If
Next nInnerIndex
Next nOuterIndex
MatchWordInSentence = "no match"
End Function
I did this in a hurry. It seems to work for me but there may be some issues. It demonstrates a principle anyway.
A further refinement would be to get the vMatchList from a cell in the spreadsheet to make it more generic.
By the way, Paul (I greet you well) and Dave G, I gave up using single letter variable names in about 1978 when I got my first Apple II. Take the plunge.
Lisa, I use Split all the time and have never noticed any speed issues with it.
Regards
Derek +++
>________________________________
> From: Paul Schreiner <schreiner_paul@att.net>
>To: ExcelVBA@yahoogroups.com
>Sent: Thursday, 23 August 2012, 17:55
>Subject: 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]
>
>
>
>
>
[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