Find specific info using search string using VBA

[laavista]

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
“Re: 09-11111 for Customer Jonesâ€
The order number is not in the same location in each email title.

I am comparing two worksheets to determine the order numbers which do not
have corresponding emails.

The following works to locate the order number above:
Set C = .Find(ReqNum, LookIn:=xlValues)

Unfortunately, order numbers have revisions, e.g., 09-11111_2 and
09-11111_3, etc.

The “find method†searches within a string and finds 09-11111 when it comes
across an email title containing 09-11111_2. I need it to find the extract
string, e.g., 09-11111 and NOT within a revised order number such as
09-11111_2.

=========
Example:
Find “09-11111â€

“This confirms your orders, 09-11111_2†(should not find 09-11111 in this
example)
“Re: 09-11111 for Customer Jones†(SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.

 

[laavista]

THIS IS A DUPLICATE QUESTION. SORRY. WHEN I "POSTED", IT SAID SERVICE WAS
TEMPORARILY NOT AVAILABLE, SO I THOUGHT I HAD TO RECREATE THE QUESTION.

 

[Martin Fishlock]

Hi:

You need to add a space after the code as in "09-11111 "

This could be done with

Set C = .Find(trim(ReqNum) & " ", LookIn:=xlValues)

There may be issues with the internet space code so you may need to do this:

Set C = .Find(trim(ReqNum) & chr(127), LookIn:=xlValues)

and then combine them.

 

[laavista]

This worked great, except IF the order number is at the end of the email
title, e.g.,
"Order for Carrie Jones, 09-11111"
it did not find it as it was expecting a blank at the end. I'm thinking I
could try to figure out how to just go through and add a space at the end of
each email title.
Any other suggestions?

THANK YOU for your quick response. This was a great, easy suggestion. I am
having trouble seeing the trees for the forest.

Thanks again.

 

[Martin Fishlock]

It may be easier to add a spcae to the end of the line but then you have
problems with punctuation.

No easy solution.

 

[laavista]

Thanks again for all your help!

It may be easier to add a spcae to the end of the line but then you have
problems with punctuation.

No easy solution.