I have been using a function similar to this to search within a larger string within a variable cell. From what I have seen, I am successfully able to count how many times "Apple" shows up within a larger string. How is this possible? From what I have seen about the InStr function it seems like I need to add a sub string. Any help or explanation is much appreciated.
Please Login or Register to view this content.
Last edited by msolari; 12-18-2012 at 01:57 PM.
InStr will only return the position of the first match after the starting point.
You can't really use it for counting the no of times a substring appears in a string.
If posting code please use code tags, see here.
To count the number of times a string appears in a larger string try:
Please Login or Register to view this content.
Thanks for the reply! I understand where you are coming from. Why then though does the function above work so well. It will find "Apple" within a larger string satisfying the If statement thus adding to my array count. I like to understand why things work when they do
Ah, right.
Instr either returns a 0 if the sub-string is not found, or a positive integer, indicating its position, if it is.
VBA interprets 0 as FALSE and any positive integer as TRUE, so the If statment works fine
Just to be clear, In my last comment I am referring to my original code. Not the code Andrew was so kind to provide.
it depends what you want as the answer if 'apple' appears twice in the same cell?
if it should count as 1 then instr is exactly the function you want, since it searches for the location of the match and returns 0 if there isn't one (which is equivalent to False in the If statement)
if it should count as 2 then you need Marcol's function
Josie
if at first you don't succeed try doing it the way your wife told you to
Your code will find Apple but it will only find it once.
Andrew you are quick to reply. Thanks for your help. One more question. So "Apple" in my code counted as the sub string? That's what is most confusing. From what I see about InStr the sub string would come after "Apple" in the original example. Here is an example of finding pp within Apple I think. If the sub string is separated by a comma then how does "Apple" in the original example become the sub string?
Please Login or Register to view this content.
the syntax for InStr is this.
InStr([Start], [String1], [String2], [Compare As VbCompareMethod = vbBinaryCompare])
[Start] - the position you want to start looking from.
[String1] - the string you want to look in.
[String2] - the string you want to look for. ie the substring
[Compare] - type of comparison to make.
[Start] and [Compare] are optional.
Last edited by Norie; 12-18-2012 at 01:39 PM.
Because whatever is in Range("D" & i) is providing the main string and "apple" is the sub-string within that.
Ah thanks guys! Very helpful. So Range("D" & i) is not considered the [Start]. It is considered [String1]. That's what was hanging me up.
Oh and yes, I am only searching if "Apple" appears once. If I ever need to search for more than one instance I will use the code provided earlier. Thanks again. Marking as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks