Is there a way to search for a large number of characters?

[Gavinmk]

I need to know which sheets the following paragraph shows up in:

Afghanistan;Albania;Algeria;Angola;Argentina;Armenia;Australia;Austria;Azerbaijan;Bahamas;Bahrain;Bangladesh;Barbados;Belarus;Belgium;Belize;Bermuda;Bolivia;Bosnia & Herzegovina;Botswana;Brazil;Brunei;Bulgaria;Cameroon;Canada;Cape Verde;Cayman Islands;Chile;Colombia;Costa Rica;Côte d'Ivoire;Croatia;Curacao;Cyprus;Czech Republic;Denmark;Dominican Republic;Ecuador;Egypt;El Salvador;Estonia;Ethiopia;Faroe Islands;Fiji;Finland;France;Georgia;Germany;Ghana;Greece;Guatemala;Honduras;Hong Kong;Hungary;Iceland;India;Indonesia;Iraq;Ireland;Israel;Italy;Jamaica;Japan;Jordan;Kazakhstan;Kenya;Korea;Kuwait;Kyrgyzstan;Latvia;Lebanon;Libya;Liechtenstein;Lithuania;Luxembourg;Macau SAR;Macedonia;Malaysia;Malta;Mauritius;Mexico;Moldova;Monaco;Mongolia;Montenegro;Morocco;Namibia;Nepal;Netherlands;New Zealand;Nicaragua;Nigeria;Norway;Oman;Pakistan;Palestinian Territories;Panama;Paraguay;Peru;Philippines;Poland;Portugal;Puerto Rico;Qatar;Romania;Russia;Rwanda;Saudi Arabia;Senegal;Serbia;Singapore;Slovakia;Slovenia;South Africa;Spain;Sri Lanka;St. Kitts and Nevis;Sweden;Switzerland;Taiwan;Tajikistan;Tanzania;Thailand;Trinidad & Tobago;Tunisia;Turkey;Turkmenistan;U.S. Virgin Islands;Uganda;Ukraine;United Arab Emirates;United Kingdom;United States;Uruguay;Uzbekistan;Venezuela;Vietnam;Zambia;Zimbabwe;



Is there a way to do this? Right now, the search function doesn't allow this many characters.

 

[ismii]

VBA?

Find can only go to 255 characters, and that string is over 1k.

You can search for part of it (the "left(string, 255)" part), then compare if it is the actual string.

EG

Option Explicit


Sub test()


Dim strSearch As String
 strSearch = "Afghanistan;Albania;Algeria;Angola;Argentina;Armenia;Australia;Austria;Azerbaijan;Bahamas;Bahrain;" & _
"Bangladesh;Barbados;Be larus;Belgium;Belize;Bermuda;Bolivia;Bosnia & Herzegovina;Botswana;Brazil;" & _
"Brunei;Bulgaria;Cameroon;Canada;Cape Verde;Cayman Islands;Chile;Colombia;Costa Rica;Côte d'Ivoire;" & _
"Croatia;Curacao;Cyprus;Czech Republic;Denmark;Dominican Republic;Ecuador;Egypt;El Salvador;Estonia;" & _
"Ethiopia;Faroe Islands;Fiji;Finland;France;Georgia;Germany;Ghana;Greece;Guatemala;Honduras;Hong Kong;" & _
"Hungary;Iceland;India;Indonesia;Iraq;Ireland;Israel;Italy;Jamaica;Japan;Jordan;Kazakhstan;Kenya;Korea;" & _
"Kuwait;Kyrgyz stan;Latvia;Lebanon;Libya;Liechtenstein;Lithuania;Luxembourg;Macau SAR;Macedonia;Malaysia;" & _
"Malta;Mauritius;Mexico;Moldova;Monaco;Mongolia;Montenegro;Morocco;Namibia;Nepal;Netherlands;New Zealand;" & _
"Nicaragua;Nigeria;Norway;Oman;Pakistan;Palestinian Territories;Panama;Paraguay;Peru;Philippines;Poland;" & _
"Portugal;Puerto Rico;Qatar;Romania;Russia;Rwanda;Saudi Arabia;Senegal;Serbia;Singapore;Slovakia;Slovenia;" & _
"South Africa;Spain;Sri Lanka;St. Kitts and Nevis;Sweden;Switzerland;Taiwan;Tajikistan;Tanzania;Thailand;Trinidad & " & _
"Tobago;Tunisia;Turkey;Turkmenistan;U.S. Virgin Islands;Uganda;Ukraine;United Arab Emirates;United Kingdom;" & _
"United States;Uruguay;Uzbekistan;Venezuela;Vietnam;Zambia;Zimbabwe;"


Dim rngFound As Range


Set rngFound = ActiveSheet.UsedRange.Find( _
                                What:=Left(strSearch, 255), _
                                Lookat:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=True)
MsgBox rngFound.Address


If rngFound.Value = strSearch Then
    MsgBox "True"
Else
    MsgBox "False"
End If


End Sub

Then you can loop through all the sheets, seeing where it all is located.

Edit: You'll also need to spread the string out among a few lines in the IDE (as I did) as the lines in the IDE don't allow it to be that long.