+ Reply to Thread
Results 1 to 3 of 3

Find function for forms

  1. #1
    Registered User
    Join Date
    11-04-2006
    Posts
    19

    Find function for forms

    Hi.

    I was wondering how to get the find function working in forms.

    On a spreadsheet it's fine. I just type in:

    =Find("what-Eh-verrr",B5,1)
    and it will look in cell B5 for the string: what-Eh-verrr
    If it finds it then it returns the position of 'what-Eh-verrr' within B5.
    E.g. B5 contains "yeah you guys - what-Eh-verrr" then the find function will count 17 characters. There are 16 characters before 'what-Eh-verrr', and for some reason it counts the string 'what-EH-verrr' as 1 as well.

    Now I have a TextBox on a UserForm containing the immortal lines from the cartoon South Park:
    '[Stan:] Cartman, you have a satellite dish coming out of your anus.
    [Cartman:] Yeah you guys - what-Eh-verrr.'

    I want to count the number of characters before the 'whatever'. There must be a way to do it. Unfortunately my brain is small.

    I have tried:
    x = Find("whatever", TextBox1,1)
    but the computer stares at me coldly and says "find? that's not a word!"

    Please help!
    Last edited by heinousanus; 11-27-2006 at 01:54 AM.

  2. #2
    Registered User
    Join Date
    11-04-2006
    Posts
    19
    I've been looking at sticking the contents of the TextBox in a cell on a worksheet, then applying the Find function to it. It feels messy but I think it works:

    Private Sub Count_No_Of_Characters_Before_The_String_Whatever_Click()

    'Put the contents of TextBox1 into the cell A1:

    Range("A1") = TextBox1

    'In cell B1, place the find function. It looks at A1 and counts the number
    'of characters appearing in the cell before the string: whatever

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=FIND(""whatever"",RC[-1],1)"

    'Place the result of the find function in TextBox2:

    TextBox2 = Range("B1")

    End Sub

    ( To get this code to work you will need a userform containing: )
    2 textboxes named TextBox1 and TextBox2, and a button named Count_No_Of_Characters_Before_The_String_Whatever

    I would prefer just to go straight from TextBox1 to TextBox2, and not TextBox1 to A1 to B1 to TextBox2. Any ideas?

  3. #3
    Registered User
    Join Date
    11-04-2006
    Posts
    19
    Ok I know I am completely just talking to myself but:

    I was just looking at www.techonthenet.com/excel/formulas and they have explanations of different functions.

    I found a functionm, InStr that works just like the find function but works in Userforms as well.

    Textbox2 = InStr(1,TextBox1, "what-Eh-verrr")

    Looks in TextBox1 for the position of the word in the quotation marks. In TextBox2, it indicates the position by giving the number of characters before the word "what-eh-verrr".
    Last edited by heinousanus; 11-28-2006 at 04:36 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1