Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text or part of a text inside the value cell
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding text or part of a text inside the value cell
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 wrote in message ... 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, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to embed large number of variables in text inside a text box? | Excel Discussion (Misc queries) | |||
I NEED TO CAPITIALIZE MY TEXT INSIDE THE CELL...HOW? | Excel Discussion (Misc queries) | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
Finding text in a cell and returning a value based on that text | Excel Discussion (Misc queries) | |||
Finding specific text in string - Part II | Excel Worksheet Functions |