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

__._,_.___

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

Messages in this topic (19)

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

.

__,_._,___