Finding text or part of a text inside the value cell

[felipe.junqueira]

Hi Focus,

I am trying to compare 2 values in different cell in separeted
workbooks, but the value of cell are different. So I need to use some
"substring" funcition to compare a part of value inside the cell with
another cell. For example:
Cell1 = ZONE 101
CellX (in other workbook) = STOP XXXX ZONE 101 YYYYY

The part of text is the same, so I need to find cell1 and compare to
cellX and when I find this part of text I need do copy 2 other cell in
2 other places.

Can someone help me to do this in macros VBA?

Rgrds,

 

[Rick Rothstein \(MVP - VB\)]

Based on your Subject line, I'm guessing you are having trouble with the
"find" part and not the "copy" part. VBA has an InStr function which will
return the starting character position of a substring within a larger string
of text... if the substring is not contained within the larger string of
text, the function returns zero... so, you can use that to test. The InStr
function has an odd syntax in that it has an optional **first** argument. If
you simply want an exact match (case sensitive) search starting at the
beginning of the larger text string, you only need two arguments...

Position = InStr(StringOfText, SubString)

However, if you want to do a case insensitive search, you must specify the
optional first argument (the starting position within the larger text string
to begin looking) in order to be able to specify the optional fourth
argument (where you can specify case sensitivity)...

Position = InStr(StartSearchAt, StringOfText, SubString, Casing)

Look InStr up in the help files for complete details. Anyway, for your
purposes, you will want something like this...

If InStr(CellXvalue, Cell1value) > 0 Then
' The text in CellX is inside the text in Cell1
' so put your code here
End If

The above is a case sensitive search; if you want a case insensitive search,
then you will want something like this...

If InStr(1, CellXvalue, Cell1value, vbTextCompare) > 0 Then
' The text in CellX is inside the text in Cell1
' so put your code here
End If

Rick