Results 1 to 7 of 7

Thread: Copy Paste (Capture) an action word into empty cell

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    29

    Question Copy Paste (Capture) an action word into empty cell

    Working w/ contracting/quotes, etc. "CONTRACTOR SHALL" statements and want to be able locate the word that follows the statement "Contractor Shall" in every line then copy/paste that single word into either Col A or C (whichever is easiest programmatically).. My example shows it in Col A.

    So, when it's done, it will look like the attached image: (and like statements can be grouped)

    Name:  shall-stmts.GIF
Views: 123
Size:  44.8 KB

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Paste (Capture) an action word into empty cell

    looking at your example as best as i can see, your line 2 does not follow the requirement

    you can copy this formula into c2 and drag it down the column as far as you need
    =MID(C2,FIND("contractor shall ",C2,1)+ LEN("contractor shall "),FIND(" ",C2,FIND("contractor shall ",C2,1)+1 -FIND("contractor shall ",C2,1))-1)
    test to see if it does as you require
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    29

    Re: Copy Paste (Capture) an action word into empty cell

    Westconn1, thank you - yes, you are correct, line 2 was not updated properly -- it should reflect the word "provide". (just like all the examples below it, --- it should be picking up the full word)

    When I pasted in your solution to test it -- it returns "pro" (but not the full word that follows "Contractor Shall")...
    Looks like it's limited to only 3 characters if I drag it downward..
    That's why I was thinking VBA might be necessary to make it pick up the word (as the number of characters varies all the way down with different action verbs).. There's a way to make it pick up everything up until a blank space occurs (that way it captures the full word no matter what the length of that word is)... once the vba gets to an empty space (non character) it knows to stop...

    provide
    conduct
    establish
    coordinate
    negotiate
    identify
    build

    etc

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,522

    Re: Copy Paste (Capture) an action word into empty cell

    It works, but the thrid parameter of the mid function needs some tweaking...

    I almost had it working...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Paste (Capture) an action word into empty cell

    There's a way to make it pick up everything up until a blank space occurs
    that is what it is supposed to do, it worked with a sample i tried, i will look at it further

    of course with vba it would be much simpler to code but not as efficient as using in-built functions in a formula

    here is a function that appears to work correctly on several samples, the original formula should have been doing the same thing, but on a single line without the help of variables
    Code:
    Function getnextword(s As String) As String
    Dim pos As Integer, pos1 As Integer
    pos = InStr(s, "contractor shall ") + Len("contractor shall ")
    pos1 = InStr(pos + 1, s, " ")
    If pos1 - pos > 1 Then
        getnextword = Mid(s, pos, pos1 - pos)
        Else
        getnextword = Mid(s, pos) ' this is case the next word is the last word in the cell, so no space to find
    End If
    call like =getnextword(c2)

    i will look at the original formula again later
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Sep 2005
    Location
    OK - USA
    Posts
    29

    Re: Copy Paste (Capture) an action word into empty cell

    How can I set it up to run it as a Sub?

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Copy Paste (Capture) an action word into empty cell

    this sub will use the function above, or it could all be incorporated together
    Code:
    Sub doall()
    rw = 2
    Do
        If IsEmpty(Cells(rw, 3)) Then Exit Do
        Cells(rw, 1) = getnextword(Cells(rw, 3))
        rw = rw + 1
    Loop
    End Sub
    processes all rows until an empty cell in column C
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width