Find the position of the Nth occurence in a string

[lala]

Hi, i need to be able to search a very long string (about 50,000 chars) and determine a position of the 1000th comma, then the 2000th and so on

i found a function that does exactly what i need but with shorter strings, it overflows with 50K chars

Public Function CharPos(SearchString As String, Char As String, Instance As Long)
     
    Dim x As Integer, n As Long
     
    For x = 1 To Len(SearchString)
        CharPos = CharPos + 1
         
         If Mid(SearchString, x, Len(Char)) = Char Then n = n + 1
         
        If n = Instance Then Exit Function
    Next x
     
    CharPos = CVErr(xlErrValue)
     
End Function

any ideas? thank you

 

[pbaldy]

Try declaring x as Long instead of Integer.

 

[boblarson]

Besides what Paul said, something is missing from the function. The return type.

Public Function CharPos(SearchString As String, Char As String, Instance As Long) [B][COLOR=red]As Long[/COLOR][/B]

 

[lala]

thank you and it's working now!!!!
Bob, i will put that in as well, even though i think i understand why it has to be there, i'm not sure why it worked without it

 

[lala]

nope, it's not working with the last long

 

[boblarson]

nope, it's not working with the last long

Not sure I understand what you mean by that statement.

i'm not sure why it worked without it

I am surprised you didn't get a compile error because you have this code:

CharPos = CVErr(xlErrValue)

which is setting the value of the function but if there is no return type defined I usually get a compile error. But maybe it did it as VARIANT by default.

 

[DCrake]

Public Function CharPos(SearchString As String, Char As String, Instance As Long)
     
    Dim x As Integer, n As Long
     
    For x = 1 To Len(SearchString)
        CharPos = CharPos + 1
         
         If Mid(SearchString, x, Len(Char)) = Char Then n = n + 1
         
        If n = Instance Then Exit Function
    Next x
     
    CharPos = CVErr(xlErrValue)
     
End Function

I would modify this as follows

Public Function CharPos(SearchString As String, Char As String, Instance As Long) As Long
     
    Dim x As Integer, n As Long, ChrCount As Long
     
    For x = 1 To Len(SearchString)
                 
         If Mid(SearchString, x, Len(Char)) = Char Then
            ChrCount = ChrCount +1           
         End If
         
        If ChrCount = Instance Then 
           Exit For
        End If
    Next x
     
    '/ CharPos = CVErr(xlErrValue) 'Don't understand this line
     CharPos = x
     '/Where x = the position in the search string where the nth instance of the search string commences
End Function

 

[Brianwarnock]

CharPos = CVErr(xlErrValue)

I don't know what actually happens but this line is only executed if an instance is not found.

Brian

 

[DCrake]

That being the case Brian then further amendmend needed

Public Function CharPos(SearchString As String, Char As String, Instance As Long) As Long
     
    Dim x As Integer, n As Long, ChrCount As Long
    Dim bFlag as Boolean
 
    For x = 1 To Len(SearchString)
                 
         If Mid(SearchString, x, Len(Char)) = Char Then
            ChrCount = ChrCount +1           
         End If
         
        If ChrCount = Instance Then 
           bFlag = True
           Exit For
        End If
    Next x
     
    '/ CharPos = CVErr(xlErrValue) 'Don't understand this line
     If bFlag = True then
         CharPos = x
     Else
         CharPos = 0
     End If

     '/Where x = the position in the search string where the nth instance of the search string commences
     '/If an instance was not found then the function returns 0. For example
     '/If there wa only 990 commas in the string and the argument was 1000 then the function would return 0. If it returned a value then the user would be under the impresion that the string existed at the desired juncture.

End Function

 

[lala]

sorry, i mean to say that once you add AS LONG to the end of the declarations it doesn't work

it works with the X as long instead of integer and now processes huge strings

 

[lala]

CharPos = CVErr(xlErrValue)

I don't know what actually happens but this line is only executed if an instance is not found.

Brian

yeap, that's what i was trying to say, Bob. once I added AS LONG, as you suggested, then it started breaking and highlighting this line
CharPos = CVErr(xlErrValue)

 

[DCrake]

As a matter of interest are you using this code in Excel or Access?

 

[lala]

and one more reatrted thing i did, when posting this guy's function i deleted all the comments, trying to make it shorter for you, guys, to read. He explains why that line is there, here's the original version

Public Function CharPos(SearchString As String, Char As String, Instance As Long)
     'Function purpose:  To return the position of the (first character of the )
     'nth occurance of a specific character set in a range
     
    Dim x As Long, n As Long
     
     'Loop through each letter in the search string
    For x = 1 To Len(SearchString)
         'Increment the number of characters search through
        CharPos = CharPos + 1
         
         'check if the next character(s) match the text being search for
         'and increase n if so (to count how many matches have been found
        If Mid(SearchString, x, Len(Char)) = Char Then n = n + 1
         
         'Exit loop if instance matches number found
        If n = Instance Then Exit Function
    Next x
     
     'The error below will only be triggered if the function was not
     'already exited due to success
    CharPos = CVErr(xlErrValue)
     
End Function

 

[lala]

As a matter of interest are you using this code in Excel or Access?

Access and thank you, i'm using yours now

 

[DCrake]

You see the line CharPos = CharPos + 1 was concerning me as this incremental counter ws the same name as the function itself and as such seemed to be recursive. You should really only pass a value to the return value once at the end ofthe function.

 

[lala]

You see the line CharPos = CharPos + 1 was concerning me as this incremental counter ws the same name as the function itself and as such seemed to be recursive. You should really only pass a value to the return value once at the end ofthe function.

so what would you have done?

and to further answer your question, i'm almost sure this function was written for Excel, but i tested it in Access and it worked.

 

[Brianwarnock]

yeap, that's what i was trying to say, Bob. once I added AS LONG, as you suggested, then it started breaking and highlighting this line
CharPos = CVErr(xlErrValue)

This is because the value generated here cannot be a long, that is why the CharPos was allowed to default to Variant

Brian

 

[DCrake]

The giveaway was the xlErrValue syntax. That's why I questioned it. VBA in Excel can be different from Access in many ways

 

[lala]

here's what i'm trying to do, i'm almost sure that i'm not doing this in the best way possible.
i'm creating a pass-through query in VBA and my criteria is more than 1000 entries (Oracle only takes 1000 at a time like this p.otherid in (1,2,3,4...999,1000).
Since i need to have 7000 (in this case) i was trying to break them up into 1000s.
How would you have done it? i can't just upload the values into a table in oracle and join them because there's no way to do it automatically

 

[Brianwarnock]

You see the line CharPos = CharPos + 1 was concerning me as this incremental counter ws the same name as the function itself and as such seemed to be recursive. You should really only pass a value to the return value once at the end ofthe function.

I've done this many times, usually in Excel, but cannot see anything wrong in it.

Brian