Find cells that contain text

[AMH]

Hi, as part of a loop I am trying to find cells that contain a string and
then copy some adjacent cells into another sheet. I can do the copy part no
problem but I am struggling with finding the string, I am using the InStr
command but not sure if I ma making this to complicated, this is code I am
struggling with :

If InStr(string, Cells(bb, 65)) = 1 Then
With string being the string I am searching for and bb being the row number
and part of the loop

The formula will recognise a complete string, but not part of string i.e.

String = abcd_
Cell contains abcd_ match and I can copy data
If cell contains abcd_efg no match and I want to take data from this row

I hope this makes sense and someone can help me

Thanks

 

[Rick Rothstein]

You have your arguments reversed... InStr is not the syntax equivalent of
the FIND function... the string you want to search is the first argument and
the substring you want to find is the second argument. However, InStr has an
optional first argument (yeah, that is strange, but that is how the function
works) and optional fourth argument as well, so you should check out the
help files for InStr so you can see how to use all of its parts.

 

[Mike H]

Hi,

Try this

If InStr(1, Cells(bb, 1), mystring, vbTextCompare) > 0 Then

Note this will find a string with a longer string and isn't case sensitive.
Also you were using a variable called 'string' and this is a reserved word so
you can't do that
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[AMH]

Thanks Mike tried that but still same result, I was not using string it was
just an example, here is the real code I have tried using :

If InStr(1, Cells(bb, 65), opasheetcode1, vbTextCompare) > 0 Or InStr(1,
Cells(bb, 65), opasheetcode2, vbTextCompare) > 0 Then

With opasheetcode1 or opasheetcode2 being two different strings of text I am
looking for, the 1st string contains AI_AIMS which it recognises, however I
also want to identify AI_AIMS_WWS (which is one of many variables to end of
string AI_AIMS), which it does not recognise thus I don’t get any data back
for the 2nd string

 

[Mike H]

Hi,

Two text strings

AI_AIMS_
AI_AIMS_WWS

The INSTR function your using with OR will 'never' find the second string
(AI_AIMS_WWS) because the first one (AI_AIMS_) will already have evaluated as
TRUE and INSTR stops evaluating as soon as TRUE condition is encountered.

If 'Many' variable begin A1_MMS_ then perhaps you can ignore looking for
this bit and instead just look for the last 3 characters.

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Are you saying your variables are set like this...

opasheetcode1 = "AI_AIMS"
opasheetcode2 = "AI_AIMS_WWS "

Are you saying the first InStr works but the second one doesn't? Given the
If..Then statement you showed us, how do you know that? You have an "Or"
test, so if either one is True, then the "If" statement evaluates to True
even if the second one didn't work (although I don't see how it couldn't
work if your variables are assigned as above and if you didn't spell the
second code incorrectly either in the assignment or in the text within the
cell). Maybe your problem is elsewhere in your code and not in the If..Then
statement you showed us. If you need the locations of each code string
above, that is doable, but it would be better to use an array rather than
the hard-coded variable names you used.