Find specific info using search string using VBA
 

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
€œ 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)
€œ 09-11111 for Customer Jones€� (SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.

Find specific info using search string using VBA
 

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.



"laavista" wrote:

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
€œ 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)
€œ 09-11111 for Customer Jones€� (SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.

Find specific info using search string using VBA
 

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.
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"laavista" wrote:

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
€œ 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)
€œ 09-11111 for Customer Jones€� (SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.

Find specific info using search string using VBA
 

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" wrote:

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.
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"laavista" wrote:

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
€œ 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)
€œ 09-11111 for Customer Jones€� (SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.

Find specific info using search string using VBA
 

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

No easy solution.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"laavista" wrote:

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" wrote:

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.
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"laavista" wrote:

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
€œ 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)
€œ 09-11111 for Customer Jones€� (SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.

Find specific info using search string using VBA
 

Thanks again for all your help!

"Martin Fishlock" wrote:

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

No easy solution.

--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"laavista" wrote:

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" wrote:

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.
--
Hope this helps
Martin Fishlock, www.nyfconsultants.com, Wolverhampton, UK
Please do not forget to rate this reply.


"laavista" wrote:

An order number is embedded in an email title, e.g., order number in the
email below is 09-11111
€œ 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)
€œ 09-11111 for Customer Jones€� (SHOULD find 09-11111 in this example)
==========

Any help would be GREATLY appreciated.