If InStr StartPos, EndPos, ContainsThis, Then Run This Action

[indi visual]

I am in need of a simple code that will mark 1 specific character in string as a "StartPosition", and another as an "EndPosition", and a "SearchForThis" between those two points.


As written simply as possible (any example for the start and end pos is clearly up to you for illustration purposes). The "Then" action can just be a message box I suppose.


I have similar code, but when I need to modify for other needs I run into a world of trouble with the proper syntax.


If someone could help me with this, what I'm looking for is something that will easily allow me to change the StartPos for different things, and the EndPosition for different things as needed as well.


I would like to have a code template in place like this so that whenever I need to search between two characters for specific text, all I would simply need to do is plug in the different criteria accordingly (be it 1 character or 12 characters or however many), and then specify the action to run.


I suppose a '''StartPos note, an '''EndPos note, and a '''Then run action here note in the code would be awesome.


I know it seems like I keep asking the same questions, but from now on I'm going to start asking for "plug-in modify style" template code.

 

[xld]

Can you illustrate what you mean with an example or two?

 

[Luke M]

Here's a function you could use. Example call-out in VB would be:

x = MySearch("I like Chandoo",2,8,"like")



In this example, x = True




Function MySearch(MyWord As String, StartPos As Integer, EndPos As Integer, _

SearchFor As String) As Boolean


'error handler

If StartPos < 0 Or EndPos > Len(MyWord) Then

MySearch = "#VALUE!"

Exit Function

End If


'Mid function narrows down the original word to just the

'section that we want to look at

'InStr will be 0 for errors/word not found, >0 if word found


MySearch = (InStr(1, Mid(MyWord, StartPos, EndPos - StartPos), SearchFor) > 0)


End Function

 

[indi visual]

xld | Luke

Luke based on your post I guess I'm going to need a function to accompany this sub (which is cool). Just not sure how to make them work together. Here's an example of what I could understand more clearly. In this example I'm only searching for one character, but enabling the code to allow me to search for any number of characters would be awesome. The following beneath is wrong, I just wrote it out for illustration purposes.


Sub search_between_two_words_to_see_if_criteria_is_found()

Dim C As Range

Dim StartPos As String, EndPos As String

Dim FindCriteria As String

Dim MyString As String


StartPos = "("

EndPos = ")"

FindCriteria = "}"


For Each C In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)


If (InStr(1, Mid(FindCriteria, StartPos, EndPos - StartPos), SearchFor) > 0) Like "}" Then

MsgBox "an end brace '}' was found in the middle of the startpos and end pos"

End If


Next C

End Sub

 

[Luke M]

indi,

I misunderstood your first post, I thought StartPos/EndPos was a number. Function modified to suit:


Function MySearch(MyWord As String, StartPos As String, EndPos As String, _

SearchFor As String) As Boolean

Dim xStart As Integer, xEnd As Integer

'error handler

xStart = InStr(1, MyWord, StartPos)

xEnd = InStr(1, MyWord, EndPos)


'Ensure both criteria were found, and End comes after Start

If xStart = 0 Or xEnd <= xStart Then

MySearch = "Keys not found"

Exit Function

End If


'Mid function narrows down the original word to just the

'section that we want to look at

'InStr will be 0 for errors/word not found, >0 if word found


MySearch = (InStr(1, Mid(MyWord, xStart, xEnd - xStart), SearchFor) > 0)


End Function


Your regular sub would then call-out the function (note that it's seperated out which leads to greater efficiency!). Note that you've the sub and function do not need to define things the same way. I've modified your Dims to clarify.


Sub SearchWords

Dim C As Range

Dim StartWord As String, EndWord As String

Dim FindCriteria As String


StartWord = "("

EndWord = ")"

FindCriteria = "}"


For Each C In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

'MySearch has already been defined as a Boolean, returns True/False

If MySearch(C.Value,StartWord,EndWord,FindCriteria) Then

MsgBox "Criteria Found!"

End If

Next


End sub

 

[indi visual]

Beautiful Luke.


Only last bump I'm running into is this:


'Ensure both criteria were found, and End comes after Start

If xStart = 0 Or xEnd <= xStart Then

MySearch = "Keys not found" '''it appears I keep getting a debug error here

Exit Function

End If

 

[Luke M]

Doh! I forgot I defined MySearch as a Boolean, can't be having it say a text string!


MySearch = False


This makes the assumption that if the criteria words aren't found in the correct order, then our goal (is this word found between these words?) is not met.

 

[indi visual]

Beautiful, and what I find crazy is I'm under the distinct impression you wrote this without testing it, which could only mean you know this stuff so well you could've wrote this blindfolded.


Thanks a mill.

 

[Luke M]

Always glad to help. =)