The values 250, 5000 are both in 250000

[ste mac]

Bit of help please, essentially the three macro's below do the same thing
ie find a number in a range.
eg find250 = look in the range see if 250 is there, tell me where it is...etc

The problem, there was 250000 found in the range and it told where, but
it also said 250 and 5000 where also in the same place! I presume this
is because 250 is in '250'000 and 5000 is also in 2'5000'0 so it gives the
same location for all three when they are located in different places...

Can this be resolved so the location of 250 is the real location
same for 5000 and 250000...

Thanks for any help

ste

Sub find250()
Dim lastrow As Long, rng As Range, rng1 As Range
Sheets("Locations").Range("P2").Select
lastrow = ActiveSheet.Range("S28").Value + 1
Set rng = Range("P1" & lastrow)
Set rng1 = rng.Find(Range("R24").Value)
If Not rng1 Is Nothing Then
ActiveSheet.Range("S16").Value = rng1.Row - 1
Else
ActiveSheet.Range("S16").Value = 0
End If
find5000
End Sub
Sub find5000()
Dim lastrow As Long, rng As Range, rng1 As Range
Sheets("Locations").Range("P2").Select
lastrow = ActiveSheet.Range("S28").Value + 1
Set rng = Range("P1" & lastrow)
Set rng1 = rng.Find(Range("R25").Value)
If Not rng1 Is Nothing Then
ActiveSheet.Range("S17").Value = rng1.Row - 1
Else
ActiveSheet.Range("S17").Value = 0
End If
find250000
End Sub
Sub find250000()
Dim lastrow As Long, rng As Range, rng1 As Range
Sheets("Locations").Range("P2").Select
lastrow = ActiveSheet.Range("S28").Value + 1
Set rng = Range("P1" & lastrow)
Set rng1 = rng.Find(Range("R26").Value)
If Not rng1 Is Nothing Then
ActiveSheet.Range("S18").Value = rng1.Row - 1
Else
ActiveSheet.Range("S18").Value = 0
End If
End Sub

 

[Ken Wright]

Take a look at your code and see if there is a piece that looks like

Selection.Find(What:="250", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Note the LookAt:=xlWhole bit, your's probably says LookAt:=xlPart - Just
switch it.

 

[Tom Ogilvy]

Here is Ken's suggestion applied to your code since he apparently didn't see
it.

Sub find250()
Dim lastrow As Long, rng As Range, rng1 As Range
Sheets("Locations").Range("P2").Select
lastrow = ActiveSheet.Range("S28").Value + 1
Set rng = Range("P1" & lastrow)
Set rng1 = rng.Find(Range("R24").Value _
,LookIn:=xlFormulas, LookAt:=xlWhole)
If Not rng1 Is Nothing Then
ActiveSheet.Range("S16").Value = rng1.Row - 1
Else
ActiveSheet.Range("S16").Value = 0
End If
find5000
End Sub

Sub find5000()
Dim lastrow As Long, rng As Range, rng1 As Range
Sheets("Locations").Range("P2").Select
lastrow = ActiveSheet.Range("S28").Value + 1
Set rng = Range("P1" & lastrow)
Set rng1 = rng.Find(Range("R25").Value _
,LookIn:=xlFormulas, LookAt:=xlWhole)
If Not rng1 Is Nothing Then
ActiveSheet.Range("S17").Value = rng1.Row - 1
Else
ActiveSheet.Range("S17").Value = 0
End If
find250000
End Sub

Sub find250000()
Dim lastrow As Long, rng As Range, rng1 As Range
Sheets("Locations").Range("P2").Select
lastrow = ActiveSheet.Range("S28").Value + 1
Set rng = Range("P1" & lastrow)
Set rng1 = rng.Find(Range("R26").Value _
,LookIn:=xlFormulas, LookAt:=xlWhole)
If Not rng1 Is Nothing Then
ActiveSheet.Range("S18").Value = rng1.Row - 1
Else
ActiveSheet.Range("S18").Value = 0
End If
End Sub

 

[Ken Wright]

rotflmao - Sorry Tom, I just saw the 'Thanks for any help' bit which
appeared right at the bottom of my preview pane and figured that that was
the end of the message. Never even noticed the scroll bars :-(

Cheers for the catch.

 

[Tom Ogilvy]

Ken,
I am more than familiar with that oversight <g>

 

[ste mac]

Hi Ken, thanks for the speedy reply.. I cannot see any code as you suggested
I thought the 'find' was

Set rng1 = rng.Find(Range("R24").Value)'whereas "R24" is a value of 250

Is it possible to change this statement as per your suggestion? so it looks
something like:
Set rng1 = rng.Find(What:="250", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Would that work? as you can tell I am no excel guru : )

thanks

ste

 

[Ken Wright]

LOL, Take a look at Tom's reply because his eyesight is working a lot better
than mine at the moment, and he has even shown you what bits to amend in
your code

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Hi Ken, thanks for the speedy reply.. I cannot see any code as you suggested
I thought the 'find' was

Set rng1 = rng.Find(Range("R24").Value)'whereas "R24" is a value of 250

Is it possible to change this statement as per your suggestion? so it looks
something like:
Set rng1 = rng.Find(What:="250", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

Would that work? as you can tell I am no excel guru : )

thanks

ste

<snip>

 

[ste mac]

Many thanks to both Ken and Tom....

It works like a charm...cheers

ste