[VBA] Find text in range problem (identifying text from start of string only)

[tinkst]

I had a table of text data like this:

ABCD
BCDE
CDEF
DEFG

When I tried using Range.find to search for a specific text, such as "DE", I wanted Excel to return the 4th datum ("DEFG") because it began with "DE". However, as you expect, Excel returned the 2nd datum ("BCDE") because it also contained "DE", but at the end of the string.

Anything I could do about this?
Many thanks for spending a minute or two for me to help out. Much appreciated!

 

[Rick Rothstein]

Use DE* in the "Find what" field, click the "Options" button and put a check mark in the checkbox labeled "Match entire cell contents".

 

[tinkst]

Thanks Rick!

How about if I'd like to do the searching in VBA?
My current codes are somewhat like this:

Range("A:A").Find("DE", LookIn:=xlValues)

Another problem I forgot mentioning was that, some of my data really contained an "*" in them!
So the data could be like:

ABCD
BCDE
CDEF
DEFG
FDE*

and sometimes my searching has to be done from bottom to top:

Range("A:A").Find("DE", LookIn:=xlValues, SearchDirection:=xlPrevious)

But I wanted Excel to identify the desired string only when a text data begins with that string.

Apologies for not asking clearing in the first place!
Thanks again for helping out, folks!

 

[Rick Rothstein]

Another problem I forgot mentioning was that, some of my data really contained an "*" in them!

Will you be searching for that asterisk as part of your search text?

and sometimes my searching has to be done from bottom to top:

Does this mean the text you are searching for can appear more than once in the column? If so, is you ultimate aim to find all occurrences or do you only want to find either the first or last occurrence?

 

[tinkst]

Thank you again Rick!

In my worksheet, I use a userform for users to enter a "Start string" and an "End string". Then, I use VBA to search through the data to find a text datum beginning with the "Start string", and at the same time searching from the bottom of data to find another text datum beginning with the "End string".
Lastly, the whole range from the "Start string" cell to the "End string" cell will be copied to another blank worksheet.

I don't think the user will enter that asterisk, because some of the text data really contained asterisks!

For example, when my data look like this:

ABCD
BCDE
CDEF
DEFG
EFGH
XEF*

When the user defines "DE" as the "Start string" and "EF" as the "End string", I want Excel to only copy out

DEFG
EFGH

instead of

BCDE
CDEF
DEFG
EFGH
XEF*

The data do not contain duplicates.

Thanks again!!!!

 

[Rick Rothstein]

See if you can adapt this to your own code (note that I am copying the specified range to Sheet2 cell A1 for example purposes, change as needed)...

[table="width: 500"]
[tr]
	[td]Sub CopyStartToEnd()
  Dim FindWhat As String, Start As Range, Finish As Range
  FindWhat = "[B][COLOR="#FF0000"]DE[/COLOR][/B]"
  Set Start = Columns("A").Find(FindWhat & "*", , xlValues, xlWhole, , , , , False)
  FindWhat = "[B][COLOR="#FF0000"]EF[/COLOR][/B]"
  Set Finish = Columns("A").Find(FindWhat & "*", , xlValues, xlWhole, , , , , False)
  Range(Start, Finish).Copy Sheets("[B][COLOR="#FF0000"]Sheet2[/COLOR][/B]").Range("[B][COLOR="#FF0000"]A1[/COLOR][/B]")
End Sub[/td]
[/tr]
[/table]

 

[tinkst]

It worked!!!
Thank you so much Rick!!!!