trim data and search for text strings

[NDBC]

I have id tags that when scanned turn up in excel in the following format

982 000140555698 or 982 000140555612

The actual id number is always numbers no letters has 16 spaces in the format
000 000000000000. Nearly always it turns up with the > sign in front but not
always.

I use the following code to get rid of any excess text

ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16)

The target is dim as range

I then have to use the id number to search through 2 columns of id numbers
and find the associated rider team number. Column headings are like this

Team number Rider1 Tag id Rider2 tag id

100 982 000140555698 982 000140555612
101 982 000140555816 982 000140555836
etc

To find the address of the cell where the scanned id tag is I use

Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues)

then to find the team number

Team = Sheet22.Cells(RiderCell.Row, 1)

This works well for the few scanned id tags that don't have the > in front
of the number but any id's that come in like >982 000140555698 then it
doesn't work for some reason. I have checked the mid statement and it seems
to be doing the job.

Is there a way I can set this up so that it will work for both possible
scanned inputs.

Thanks

 

[Joel]

from
Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues)
to
Set RiderCell = Sheet22.Range("b4:c804").Find(what:=Target, _
lookin:=xlValues,lookat:=xlpart)

 

[Jacob Skaria]

Try the below

Set RiderCell = Sheet22.Range("b4:c804").Find(What:=Target, _
LookIn:=xlValues, LookAt:=xlPart)

If this post helps click Yes

 

[NDBC]

Joel,

Thanks for the help but it still didn't work. I put a break in my code just
at the ridercell statement and funily enough trying to find the target value
doesn't work. I should be trying to find the ID value after i have used the
mid statement so this works

Set RiderCell = Sheet22.Range("b4:c804").Find(what:=ID, _
lookin:=xlValues,lookat:=xlpart)

Thanks. you got me there in the end anyway.

 

[Rick Rothstein]

Just for your information, you can replace this line of code...

ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16)

with this much simpler one...

ID = Replace(Target, ">", "")

 

[NDBC]

Thanks Rick, I didn't know that. I may not change it however as I am not 100%
sure that other characters may not turn up at either end of the tag number
and I know that the mid statement will work as long as there is no other
spaces in the ID. Once I have used the system for a while longer and I am
more confident in the range of input I will change it though.

Thanks