Finding an exact string in a range

[Planner999]

Hi all

I really need some help please....

I have list of values in a range of cells

18A
18B
18C
18D
18E
18

There is a string variable called Curr_DU

The line of code reads

Dim Start_Rng as Range
Dim Curr_DU as String


Set Start_Rng = Range ("C2:C50").Find(Curr_DU)

This works great until it hits the last value which is 18 but it then
picks up 18A position. How can I simply change the macro to be
specific on finding 18 but not the others.

Thanks in Advance

John

 

[Mike H]

Hi,

In the sample code below "18" is a string but Excel is very forgiving and 18
could be a number. Anyway, having found it you don't say what you want to do
so this just gives the address in a message box

Dim Start_Rng As Range
Dim Curr_DU As Long
Curr_DU = "18"
Set Start_Rng = Range("C2:C50")
For Each c In Start_Rng
If c.Value = Curr_DU Then
'do something
MsgBox c.Address
End If
Next

Mike

 

[JLGWhiz]

If you have previously used the find function and had the LookAt:=xlPart
then it will find anything with 18 in it. To cure that, change this line:

Set Start_Rng = Range ("C2:C50").Find(Curr_DU)

To:

Set Start_Rng = Range ("C2:C50").Find(Curr_DU, LookAt:=xlWhole)

It should then look for an exact match.

 

[Planner999]

Mike - This solution does not seem to work because Curr_DU can be
either String (18x) or numeric (18) depending on an earlier setting of
the variable.

JL GWhiz - I am afraid your solution does not work either. It does not
recognise finding specifically the value 18 - it still looks at the
first cell it comes across, which in this case is 18A.

Thanks to you both for your thoughts on this but I am still stuck...

John

 

[JLGWhiz]

I don't know what to tell you. I tried it several different ways to get it
to pick another value with 18 in it and it went straight to the solo 18
every time using the LookAt:=xlWhole.

 

[Planner999]

I don't know what to tell you.  I tried it several different ways to get it
to pick another value with 18 in it and it went straight to the solo 18
every time using the LookAt:=xlWhole.

Hi JLGWhiz

Solved it and it worked. - I copied your code into the macro but the :
was missing after the LookAt statement.

Intrestingly VB allowed the statement to run without errors.

Anyway thanks

John