Searching InStr With Wildcards?

[indi visual]

I'm faced with a 25 max limit line continuation problem (20 or 25 somethin' like that).


I can write this out line for line breaking it up into 25 max increments, but some of these lines are very similar and can be searched for using wildcards much easier (to knock out several searches at a time) -which would save me sometime writing out a bunch of grouped up continuations.


I've been searching online, and I've found so far InStr can't search for wildcards.


My questions is how do I achieve this anyway, what is my work around for wildcard searches?


''This WORKS (no wildcard just straight text ".findme ")

Dim SpecificText As Boolean

endrange = Range("A65536").End(xlUp).Row

For i = 1 To endrange


SpecificText = InStr(Range("A" & i).Value, ".findme ")


If SpecificText = True Then

MsgBox "Specific Straight Text Found"

End If


Next i


'This DOES NOT (it tries to look for wildcard using ".find?? ")

Dim SpecificText As Boolean

endrange = Range("A65536").End(xlUp).Row

For i = 1 To endrange


SpecificText = InStr(Range("A" & i).Value, ".find?? ")


If SpecificText = True Then

MsgBox "Specific Text Using Wildcard Found"

End If


Next i

 

[Hui]

What about using Like and pattern matching

 

[indi visual]

'Like' won't sniff out ".findme " in the middle of a string swimming in random various text, and 'InStr' will no matter what's next to it (be it a space or any character).


Dim C As Range

For Each C In Range(Cells(2, 1), Cells(2, 1).End(xlDown))

If C Like ".findme " Then

MsgBox "Won't Find Me"

End If

Next C

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim ExtentionMarker As Boolean

endrange = Range("A65536").End(xlUp).Row

For i = 1 To endrange

ExtentionMarker = InStr(Range("A" & i).Value, ".findme ")

If ExtentionMarker = True Then

MsgBox "Found Me"

End If

Next i

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


The space is probably causing my headache, but it's critical because it separates what I should be deleting from what I potentially-accidentally shouldn't be.


I will probably stick with the InStr method above and just list them all out, but if you could please help me with an array that uses the InStr method that can sniff out any text anywhere in the string like the one above I'll be super appreciative.


Right now I'm going this route (link below), and all the code is getting ridiculous.


http://www.iandmyself.me/excessive.code.sofar.txt


...


I know this is turning into a novel Hui : )


But here is the whole story so you can clearly see what I'm trying to do because perhaps you could suggest an alternate better approach all together.


There are 252 url extentions (or domains).


However, only about 10 of them will likely be in my workbook (.com, .net, .org etc)-you know the most popular ones.


Your code that you wrote for me (and I plan to mark it resolved) to remove url strings works perfectly.


But I thought it would be a good idea to check the sheet first if they exist, because all of these domains aren't even likely to be in the sheet if ever in the first place.


For example, out of all 252, first simply check to see if 240 of all those rare urls are even present in the sheet, and if not, skip the piece of code that fixes them all together saving a substantial amount of time.


I'm thinking a simple check like this could turn a five minute code into a mere 10 seconds, because with running a check first it'll likely never find a url like ".kp ", and it'll only search and replace the ones I hand picked to be the most popular urls by default after it skips it.


Something along the lines of if this rare url ".kp " in range = false (along with the others), Then UnCommonMarkers = False.


If UnCommonMarkers = False Then

Run the shorter search and replace url code


What this question for this particular post ultimately is...


How do I run a simple check on 240 or so extentions (with a space) only to see if they even exist?

 

[vijaySharma]

Indi,


Try the code below (adjust the range as necessary)

[pre]

Sub foo()

Dim myChkArr As Variant
myChkArr = Sheet1.Range("A1:A27")

myChkArr = Application.Transpose(myChkArr)
Dim z As Variant
testvalue = "ac "
z = Filter(myChkArr, testvalue)
If UBound(z) < 0 Then
MsgBox ("empty")
Else
MsgBox ":" & z(0) & ":" 'to show with spaces was found
End If

End Sub

[/pre]
~Vijay Sharma

sharma.vijay1-at-gmail.com

+91-9811996454

 

[Luke M]

Indi,


You need to encase your word in wildcard is using Like. You had:


If C Like ".findme " Then

MsgBox "Won't Find Me

End If


Should be something like:


If C Like "*.findme *" Then

MsgBox "Won't Find Me"

End If


Looking at your ultimate goal, are you sure you'll actually save any time? This plan of attack still requires you to loop through every cell for every type of URL ending.

 

[indi visual]

Hui, VJay, Luke, I appreciate the help.


Luke you're right.


Looping through the cells just to check takes more time, on top of time.


I ran a timer and it was 6 seconds longer just to check first.


I guess I shouldn't be so OCD about removing every possible link in the world.


I'll just stick with the 12 most popular links and remove only those.


VJay you're code works like a charm, but I plan to use it for something else which saves me a question.


Many thanks guys.


Resolved

 

[Hui]

@Indi

Don't use the like on the cells directly, It is the reading and re-saving that takes all the time

Use it on the array once loaded as part of the previous code I posted

That will be super fast