+ Reply to Thread
Results 1 to 6 of 6

Using the Split function with another function incorporated is not working correctly

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Using the Split function with another function incorporated is not working correctly

    Dear all, I've found and amended a small procedure called "SplitDemo" to search a string held in a cell to find a 3 digit code in that string - which works. I've tried improving it using a function called "ISLIKE" from a John Walkenback book (Excel 2010 Formulas p687) but it doesn't work. The line that seems to be causing the problem is below in the code 6 lines from the bottom. Can anyone help? I've attached a sample file, thanks, Neil

    Public Function islike(text As String, pattern As String) As Boolean
    'returns true if the first argument is like the second
    islike = text Like pattern
    End Function
    
    
    Public Sub SplitDemo2()
    
        Dim txt As String
        Dim x As Variant
        Dim i As Long
        Dim t As Boolean
        
        txt = ActiveCell.Value
        x = Split(txt, " ")
        
        For i = 0 To UBound(x)
           Debug.Print x(i)
           If Len(x(i)) = 3 Then
                If t = islike("'" & x(i) & "'", "###") Then 'Get a Byref argument type mismatch error here!
                    MsgBox x(i) & " is a match"
                End If
           End If
        Next i
    End Sub
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Using the Split function with another function incorporated is not working correctly

    It works for me .... returning ............AND ,126
    Elegant Simplicity............. Not Always

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Using the Split function with another function incorporated is not working correctly

    Yes, but according to the example the 3 # mean that the match should be a digit 0-9 so it shouldn't match against "AND".....?
    Last edited by highlystrung; 05-06-2013 at 11:30 AM.

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Using the Split function with another function incorporated is not working correctly

    highlystrung,

    Does this need to be VBA? This formula should do the trick
    =LOOKUP(2,1/(LEN(--TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),99*(ROW($1:$30)-1)+1,99)))=3),--TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",99)),99*(ROW($1:$30)-1)+1,99)))
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Using the Split function with another function incorporated is not working correctly

    Change the line
    If Len(x(i)) = 3 Then
    to
    If Len(x(i)) = 3 And IsNumeric(x(i)) Then

  6. #6
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Havant, Hants, England
    MS-Off Ver
    Excel 2010
    Posts
    116

    Re: Using the Split function with another function incorporated is not working correctly

    Thanks Andy for the coding input - it works well. Don't know why the ISLIKE procedure isn't working but then....

    thanks also to tigervator for your Lookup equation. I haven't tried it as I'm doing this all in code - I seem to work in code more than using formula in the spreadsheet ... why I don't know, maybe it's a reflection of how I've learnt EXCEL.. But it's important to say I just don't know how you work out these complex formula, they seem mind blowingly complex but maybe you have a way to do it!?!

    regards, Neil

+ 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